Hello
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)
