Create Index on new column for large table in Oracle

There are ways to make it faster but they may not be necessary.

10 million rows is a relatively small number. Although things could be different if the rows are extremely wide. For performance issues it's often better to know the segment size than the row count. The segment size and knowledge of your hardware will help you make very rough estimates. For example, "the table is 100GB, the SAN reads single-threaded at 100MB/second, so it will take 17 minutes just to scan the table...".

--Find the segment size in gigabytes.--No matter how many rows there are this may be the amount of I/O processed.select sum(bytes)/1024/1024/1024 gbfrom dba_segmentswhere segment_name = 'MYTABLE';

In this trivial example, 10 million rows are created in 5 seconds on my PC.

--Create table.drop table myTable;create table myTable(id number, myColumn varchar2(100)) nologging;--Insert 10 million rows.  Takes 9 seconds on my PC.begin    for i in 1 .. 100 loop        insert /*+ append */ into myTable          select level, null from dual connect by level <= 100000;        commit;    end loop;end;/--Create index.  Takes 5 seconds on my PC.create index myTable_idx_myColumn on myTable(myColumn);

So what's happening on your machine? To find out, first you need to find the SQL_ID for the CREATE INDEX ... statement. While the index is building, run this:

--Find the SQL_ID.select sql_id, sql_text, elapsed_time/1000000 secondsfrom v$sqlwhere users_executing > 0order by seconds desc;

There are plenty of ways to go from here, I prefer SQL Monitoring. If the statement is running or was running "recently", the monitoring data should still be around. Plug the SQL_ID into this SQL statement to get the report:

--Generate SQL Monitoring report.--(This feature requires licensing, but if this is the first time you use it, it's-- reasonable to consider this "testing".  Buy it if you like it.)select dbms_sqltune.report_sql_monitor('gb7tu2jpwng3q') from dual;

There's a lot of data in the report. It takes a while to understand, but usually it will contain most of what you need to solve these kinds of problems. First, look at the Activity (%) - which step is taking the longest? Then look at the Details - what was it waiting for? Look at the Read and Write bytes, are they reasonable for the hardware?

SQL Monitoring ReportSQL Text------------------------------create index myTable_idx_myColumn on myTable(myColumn)Global Information------------------------------ Status              :  DONE                               Instance ID         :  1                                  Session             :  JHELLER (133:56633)                SQL ID              :  gb7tu2jpwng3q                      SQL Execution ID    :  16777216                           Execution Started   :  10/23/2015 00:34:32                First Refresh Time  :  10/23/2015 00:34:36                Last Refresh Time   :  10/23/2015 00:34:37                Duration            :  5s                                 Module/Action       :  PL/SQL Developer/SQL Window - New  Service             :  orcl12                             Program             :  plsqldev.exe                      Global Stats================================================================================================| Elapsed |   Cpu   |    IO    | Application | PL/SQL  | Buffer | Read | Read  | Write | Write || Time(s) | Time(s) | Waits(s) |  Waits(s)   | Time(s) |  Gets  | Reqs | Bytes | Reqs  | Bytes |================================================================================================|    4.72 |    2.67 |     1.84 |        0.21 |    0.00 |  15594 | 3904 | 312MB |   795 | 192MB |================================================================================================SQL Plan Monitoring Details (Plan Hash Value=564701026)========================================================================================================================================================================================================| Id |        Operation         |         Name         |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Read | Read  | Write | Write |  Mem  | Temp  | Activity |      Activity Detail       ||    |                          |                      | (Estim) |      | Active(s) | Active |       | (Actual) | Reqs | Bytes | Reqs  | Bytes | (Max) | (Max) |   (%)    |        (# samples)         |========================================================================================================================================================================================================|  0 | CREATE INDEX STATEMENT   |                      |         |      |         2 |     +4 |     1 |        1 |      |       |       |       |       |       |          |                            ||  1 |   INDEX BUILD NON UNIQUE | MYTABLE_IDX_MYCOLUMN |         |      |         2 |     +4 |     1 |        1 |      |       |       |       |       |       |    25.00 | Cpu (1)                    ||  2 |    SORT CREATE INDEX     |                      |    100K |      |         4 |     +2 |     1 |      10M | 3656 | 192MB |   795 | 192MB |   75M |  202M |    75.00 | Cpu (2)                    ||    |                          |                      |         |      |           |        |       |          |      |       |       |       |       |       |          | direct path write temp (1) ||  3 |     TABLE ACCESS FULL    | MYTABLE              |    100K |   46 |         1 |     +4 |     1 |      10M |  248 | 120MB |       |       |       |       |          |                            |========================================================================================================================================================================================================

I expect you'll see some "weird" events. Perhaps some sort of table lock because some other process is locking the table.

If it's just a massive table and it takes hours to read it, then parallelism might help. Here's the simplest way to make it work. Tuning parallelism can be difficult, but if you're lucky and everything is configured sanely, just adding the keyword parallel works great.

--Create index in parallel.create index myTable_idx_myColumn on myTable(myColumn) parallel nologging;--Reset it to NOPARALLEL after it's done.alter index myTable_idx_myColumn noparallel;