ONLINE INDEX CREATION

Does Oracle support building an index using both PARALLEL AND ONLINE clause?

I checked the 12c documentation below and the only restriction regarding parallel is that it does not support parallel DML. There is no mention of not supporting parallel index creation. 11g document is a lit bit more vague.

I tried below, and it shows a parallel execution plan. However, parallelism was not being used fro what I could see.

PLAN_TABLE_OUTPUT

SQL_ID f46mg3sxn40wq, child number 0

CREATE INDEX BI_ADD_FAILED_PIECE_SCAN_D_I7 ON BI_ADD_FAILED_PIECE_SCAN_DLY(ORGN_FAC_SEQ_ID, DESTN_FAC_SEQ_ID, ORGN_FAC_ZIP_3) INITRANS 10 NOLOGGING PARALLEL 32 LOCAL COMPRESS 3 ONLINE Plan hash value: 3405290185

| Id | Operation | Name |
E-Rows |

| 0 | CREATE INDEX STATEMENT |
| |

| 1 | PX COORDINATOR |
| |

| 2 | PX SEND QC (RANDOM) | :TQ10000
| 749K|

| 3 | PX PARTITION LIST ALL |
| 749K|

| 4 | INDEX BUILD NON UNIQUE (LOCAL)| BI_ADD_FAILED_PIECE_SCAN_D_I7
| |

| 5 | SORT CREATE INDEX |
| 749K|

| 6 | TABLE ACCESS FULL | BI_ADD_FAILED_PIECE_SCAN_DLY
| 749K|

Note

• Degree of Parallelism is 32 because of table property
• Warning: basic plan statistics not available. These are only collected when:
  • hint 'gather_plan_statistics' is used for the statement or
  • parameter 'statistics_level' is set to 'ALL', at session or system level

12c DOC

https://docs.oracle.com/database/121/SQLRF/statements_1012.htm#SQLRF00805

 *ONLINE Clause*

Specify ONLINE to allow DML operations on the table or partition during rebuilding of the index.

*Restrictions on Online Indexes *Online indexes are subject to the following restrictions:

· Parallel DML is not supported during online index building. If you specify ONLINE and subsequently issue parallel DML statements, then Oracle Database returns an error.

· You cannot specify ONLINE for a bitmap join index or a cluster index.

· For a nonunique secondary index on an index-organized table, the number of index key columns plus the number of primary key columns that are included in the logical rowid in the index-organized table cannot exceed 32. The logical rowid excludes columns that are part of the index key.

11g DOC

https://docs.oracle.com/cd/B28359_01/server.111/b28310/indexes003.htm#ADMIN11730

Creating an Index Online

You can create and rebuild indexes online. This enables you to update base tables at the same time you are building or rebuilding indexes on that table. You can perform DML operations while the index build is taking place, but DDL operations are not allowed. Parallel execution is not supported when creating or rebuilding an index online.

Thanks
Ramsankar Cheruvattath (Ram)

--
http://www.freelists.org/webpage/oracle-l