Quite a time we face a situation where we have large table with Hundreds of Millions of records(sometimes even Billions of records), and we might need to add column to that table and then add index on that new column.
We have absolute control over application, code, meaning, we can make sure that data will be not populated in column before we create index. In this case, is there a way in oracle to quickly create index? currently we try to speed it up by providing high level of parallelism, but with tables fit mutli terabyte of data, that seems to be waste or resource(Scanning whole terabytes of data knowing at end it will create index with no rows).
Is there a better way to do this? is there some hidden parameter/hint to instruct oracle that after all its empty column?
eg. you can see below index creation scanned ~6TB of data, took 9 hours, wasting tons of cpu/io resources on production system to produce index with 0 rows.
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
Sorry, no such optimization exists (to my knowledge) in any version of Oracle. For a partitioned table, you could perhaps take advantage of partially creating an index (assuming you do not intend to add historical values into the new column) to reduce the overall resource effort.
I've reached out to the VLDB group to see if they have anything to add, and I'll add anything useful to the question when I hear back.
I certainly like the idea of an immutable operation that would perform both the addition and the indexing in a similar way to constraints, eg
alter table T add MY_COL int using index ( create index T_IX on T (my_col) );
Perhaps you'd like to add it to:
https://community.oracle.com/community/database/database-ideas The voting on such ideas is taken into consideration when new features are examined for database releases.