问题描述
相当长一段时间，我们面临的情况是，我们有数以亿计的记录 (有时甚至是数十亿条记录) 的大表，我们可能需要添加列到该表中，然后在该新列上添加索引。
我们对应用程序，代码有绝对的控制权，这意味着，我们可以确保在创建索引之前不会在列中填充数据。在这种情况下，oracle中是否有快速创建索引的方法？目前，我们试图通过提供高水平的并行性来加快它的速度，但是表适合多tb的数据，这似乎是浪费或资源 (扫描整个tb的数据，知道最后它将创建没有行的索引)。
有没有更好的方法来做到这一点？是否有一些隐藏的参数/提示来指示oracle，毕竟它的空列？
例如。您可以看到下面的索引创建扫描了〜6TB的数据，花费了9个小时，浪费了生产系统上的大量cpu/io资源来生成具有0行的索引。
我们对应用程序，代码有绝对的控制权，这意味着，我们可以确保在创建索引之前不会在列中填充数据。在这种情况下，oracle中是否有快速创建索引的方法？目前，我们试图通过提供高水平的并行性来加快它的速度，但是表适合多tb的数据，这似乎是浪费或资源 (扫描整个tb的数据，知道最后它将创建没有行的索引)。
有没有更好的方法来做到这一点？是否有一些隐藏的参数/提示来指示oracle，毕竟它的空列？
例如。您可以看到下面的索引创建扫描了〜6TB的数据，花费了9个小时，浪费了生产系统上的大量cpu/io资源来生成具有0行的索引。
SQL Monitoring Report SQL Text ------------------------------ create index HR.EXAMPLE_DATA_dt on HR.EXAMPLE_DATA (deleted_ts) online compress parallel 12 invisible Global Information ------------------------------ Status : DONE Instance ID : 1 Session : GG_ADMIN (4807:35959) SQL ID : 83xf83uzg7a23 SQL Execution ID : 16777217 Execution Started : 11/07/2017 00:06:32 First Refresh Time : 11/07/2017 00:06:41 Last Refresh Time : 11/07/2017 08:51:44 Duration : 31512s Module/Action : SQL*Plus/- Service : SYS$USERS Program : sqlplus@example_host.prod.linkedin.com (TNS V1-V3) Global Stats ========================================================================================================================= | Elapsed | Cpu | IO | Application | Concurrency | PL/SQL | Other | Buffer | Read | Read | Write | Write | | Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Time(s) | Waits(s) | Gets | Reqs | Bytes | Reqs | Bytes | ========================================================================================================================= | 400666 | 51825 | 348829 | 12 | 0.10 | 0.18 | 0.09 | 956M | 414M | 6TB | 59596 | 12GB | ========================================================================================================================= Parallel Execution Details (DOP=12 , Servers Allocated=24) ================================================================================================================================================================================================ | Name | Type | Server# | Elapsed | Cpu | IO | Application | Concurrency | PL/SQL | Other | Buffer | Read | Read | Write | Write | Wait Events | | | | | Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Time(s) | Waits(s) | Gets | Reqs | Bytes | Reqs | Bytes | (sample #) | ================================================================================================================================================================================================ | PX Coordinator | QC | | 17 | 3.64 | 1.32 | 12 | 0.00 | 0.18 | 0.09 | 30850 | 1258 | 10MB | | . | enq: TX - row lock contention (6) | | | | | | | | | | | | | | | | | db file sequential read (1) | | p000 | Set 1 | 1 | 36 | 36 | 0.02 | | | | | 17 | 16 | 128KB | 4765 | 953MB | direct path read tHR (2) | | p001 | Set 1 | 2 | 65 | 63 | 2.31 | | | | | 18 | 5120 | 1GB | 5093 | 1GB | direct path read tHR (4) | | p002 | Set 1 | 3 | 69 | 67 | 2.30 | | | | | 19 | 5215 | 1GB | 5190 | 1GB | direct path read tHR (6) | | p003 | Set 1 | 4 | 35 | 35 | 0.02 | | | | | 17 | 16 | 128KB | 4775 | 1GB | direct path read tHR (1) | | p004 | Set 1 | 5 | 34 | 34 | 0.02 | | | | | 16 | 15 | 120KB | 4745 | 949MB | direct path read tHR (3) | | p005 | Set 1 | 6 | 125 | 121 | 4.04 | | | | | 33 | 9199 | 2GB | 9159 | 2GB | direct path read tHR (6) | | p006 | Set 1 | 7 | 66 | 64 | 2.33 | | | | | 20 | 5212 | 1GB | 5190 | 1GB | direct path read tHR (1) | | p007 | Set 1 | 8 | 67 | 65 | 2.42 | | | | | 19 | 5293 | 1GB | 5272 | 1GB | direct path read tHR (1) | | p008 | Set 1 | 9 | 68 | 66 | 2.34 | | | | | 20 | 5201 | 1GB | 5175 | 1GB | direct path read tHR (1) | | p009 | Set 1 | 10 | 67 | 65 | 2.30 | | | | | 19 | 5204 | 1GB | 5182 | 1GB | direct path read tHR (4) | | p00a | Set 1 | 11 | 66 | 63 | 2.32 | | | | | 19 | 5070 | 1GB | 5050 | 1GB | direct path read tHR (4) | | p00b | Set 1 | 12 | | | | | | | | | | . | | . | | | p00c | Set 2 | 1 | 33163 | 4073 | 29090 | | 0.01 | | | 79M | 35M | 523GB | | . | db file scattered read (2612) | | | | | | | | | | | | | | | | | db file sequential read (21445) | | p00d | Set 2 | 2 | 33584 | 4468 | 29116 | | 0.01 | | | 81M | 34M | 518GB | | . | db file scattered read (2656) | | | | | | | | | | | | | | | | | db file sequential read (21407) | | p00e | Set 2 | 3 | 32954 | 4067 | 28888 | | 0.01 | | | 79M | 35M | 517GB | | . | db file scattered read (2523) | | | | | | | | | | | | | | | | | db file sequential read (21394) | | p00f | Set 2 | 4 | 33065 | 4081 | 28984 | | 0.01 | | | 81M | 34M | 523GB | | . | db file scattered read (2554) | | | | | | | | | | | | | | | | | db file sequential read (21323) | | p00g | Set 2 | 5 | 33178 | 4085 | 29093 | | 0.01 | | | 80M | 35M | 522GB | | . | db file scattered read (2683) | | | | | | | | | | | | | | | | | db file sequential read (21379) | | p00h | Set 2 | 6 | 33310 | 4413 | 28897 | | 0.01 | | | 79M | 34M | 513GB | | . | db file scattered read (2538) | | | | | | | | | | | | | | | | | db file sequential read (21359) | | p00i | Set 2 | 7 | 33004 | 4077 | 28926 | | 0.01 | | | 79M | 35M | 518GB | | . | db file scattered read (2555) | | | | | | | | | | | | | | | | | db file sequential read (21333) | | p00j | Set 2 | 8 | 33482 | 4433 | 29049 | | 0.01 | | | 80M | 34M | 518GB | | . | db file scattered read (2582) | | | | | | | | | | | | | | | | | db file sequential read (21289) | | p00k | Set 2 | 9 | 33082 | 4109 | 28973 | | 0.01 | | | 81M | 35M | 525GB | | . | db file scattered read (2613) | | | | | | | | | | | | | | | | | db file sequential read (21328) | | p00l | Set 2 | 10 | 34010 | 4473 | 29536 | | 0.01 | | | 80M | 35M | 524GB | | . | db file scattered read (2532) | | | | | | | | | | | | | | | | | db file sequential read (22060) | | p00m | Set 2 | 11 | 33923 | 4476 | 29448 | | 0.01 | | | 79M | 35M | 521GB | | . | db file scattered read (2570) | | | | | | | | | | | | | | | | | db file sequential read (21784) | | p00n | Set 2 | 12 | 33196 | 4388 | 28808 | | 0.00 | | | 77M | 34M | 509GB | | . | db file scattered read (2616) | | | | | | | | | | | | | | | | | db file sequential read (21142) | ================================================================================================================================================================================================ SQL Plan Monitoring Details (Plan Hash Value=4133307506) ============================================================================================================================================================================================================== | Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Write | Write | Mem | THR | Activity | Activity Detail | | | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Reqs | Bytes | (Max) | (Max) | (%) | (# samples) | ============================================================================================================================================================================================================== | 0 | CREATE INDEX STATEMENT | | | | 9 | +31504 | 24 | 12 | | | | | | | 0.00 | enq: TX - row lock contention (6) | | | | | | | | | | | | | | | | | | Cpu (1) | | | | | | | | | | | | | | | | | | db file sequential read (1) | | 1 | PX COORDINATOR | | | | 1 | +31507 | 24 | 12 | | | | | | | | | | 2 | PX SEND QC (ORDER) | :TQ10001 | 58M | | 1 | +31504 | 11 | 8 | | | | | | | | | | 3 | INDEX BUILD NON UNIQUE | EXAMPLE_DATA_DT | | | 60 | +31445 | 11 | 8 | | | | | | | 0.01 | Cpu (38) | | 4 | SORT CREATE INDEX | | 58M | | 31252 | +253 | 11 | 471M | 45561 | 9GB | 59596 | 12GB | 1G | 13G | 0.20 | Cpu (574) | | | | | | | | | | | | | | | | | | direct path read tHR (33) | | 5 | PX RECEIVE | | 58M | 2M | 31194 | +253 | 11 | 629M | | | | | | | 0.01 | Cpu (43) | | 6 | PX SEND RANGE | :TQ10000 | 58M | 2M | 31434 | +11 | 12 | 630M | | | | | | | 0.24 | Cpu (749) | | 7 | PX BLOCK ITERATOR | | 58M | 2M | 31434 | +11 | 24 | 631M | | | | | | | | | | 8 | TABLE ACCESS FULL | EXAMPLE_DATA | 58M | 2M | 31434 | +11 | 1789 | 631M | 414M | 6TB | | | | | 99.53 | Cpu (17304) | | | | | | | | | | | | | | | | | | db file scattered read (31034) | | | | | | | | | | | | | | | | | | db file sequential read (257243) | ============================================================================================================================================================================================================== SQL> select index_name, num_rows, last_analyzed from dba_indexes where index_name= 'EXAMPLE_DATA_DT'; INDEX_NAME NUM_ROWS LAST_ANALYZED -------------------------------------------------------------------------------------------------------------------------------- ---------- ---------------- EXAMPLE_DATA_DT 0 11-07-2017 08:51
专家解答
抱歉，在任何版本的Oracle中都不存在这样的优化 (据我所知)。对于分区表，您可以利用部分创建索引 (假设您不打算将历史值添加到新列中) 来减少整体资源工作量。
我已经联系了VLDB小组，看看他们是否有什么要补充的，当我收到回复时，我会给这个问题添加任何有用的东西。
我当然喜欢不可变操作的想法，该操作将以类似于约束的方式执行添加和索引，例如
alter table T使用索引添加MY_COL int (在T (my_col) 上创建索引T_IX);
也许你想把它添加到:
https://community.oracle.com/community/database/database-ideas
在检查数据库版本的新功能时，将考虑对此类想法的投票。
我已经联系了VLDB小组，看看他们是否有什么要补充的，当我收到回复时，我会给这个问题添加任何有用的东西。
我当然喜欢不可变操作的想法，该操作将以类似于约束的方式执行添加和索引，例如
alter table T使用索引添加MY_COL int (在T (my_col) 上创建索引T_IX);
也许你想把它添加到:
https://community.oracle.com/community/database/database-ideas
在检查数据库版本的新功能时，将考虑对此类想法的投票。
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
