Oracle's CREATE INDEX command can take a hint

While experimenting with Auto DOP (parallel_degree_policy) in Oracle 12c, I stumbled upon a highly effective (and seemingly undocumented) behavior: the CREATE INDEX command can accept a PARALLEL hint.

The Discovery: Hints vs. Attributes

Typically, to create an index in parallel, you use the standard documented syntax: CREATE INDEX tab_ind1 ON tab(col1) PARALLEL 24;

However, there is a significant side effect to this method: the index retains a default degree of parallelism (DOP) of 24. This often necessitates a second command to set it back to NOPARALLEL to avoid unexpected parallel scans in future queries.

Using the PARALLEL Hint

By using a hint instead, you achieve the parallel build without the persistent DOP setting:

CREATE /*+ PARALLEL(24) */ INDEX tab_ind1 ON tab(col1); 

Why this is better:

• No Cleanup: The index is created as NOPARALLEL by default.
• Consistency: It bypasses certain Auto DOP restrictions that I encountered during testing.
• Control: If you combine the hint with the NOPARALLEL attribute (e.g., CREATE /*+ PARALLEL */ INDEX ... NOPARALLEL), the attribute wins, and no parallelism is used.

The Auto DOP Problem

I discovered this while working with a 40 GB test table (CKK) designed with 2 rows per block to simulate a large data set.

CREATE TABLE ckk NOLOGGING TABLESPACE ckk AS  SELECT rownum id, mod(rownum,5) mod5_id, rpad('x',3500,'x') filler  FROM (SELECT rownum r FROM dual CONNECT BY level <= 10000) r1,       (SELECT rownum r FROM dual CONNECT BY level <= 1000); 

The Failure of Explicit Parallelism

When I tried to create an index using the standard parallel attribute, Oracle ignored the request:

CREATE INDEX ckk$id ON ckk(id) PARALLEL 24;  -- Result: Runs with only 1 thread! 

Surprisingly, it created the index serially and then set the metadata DOP to 24. I traced this behavior back to the Auto DOP feature (parallel_degree_policy=auto). When this policy is active, Oracle's optimizer may override your explicit request for parallelism during the initial index build.

Behavioral Inconsistencies and the "Hint" Solution

The most bizarre part of this behavior is that once a single index exists on the table, subsequent index creations behave differently.

For example, after the first index is created, running a simple CREATE INDEX ckk$mod5_id ON ckk(mod5_id); (without any parallel keywords) might suddenly trigger a parallel build automatically.

Why the Hint Wins

The /*+ PARALLEL */ hint acts more consistently than the PARALLEL attribute. In my tests, the hint forced the parallel execution even when the standard syntax failed due to Auto DOP interference. It provides a "cleaner" build by ensuring the index remains NOPARALLEL in the data dictionary once the operation completes.

Oracle Database Consulting Services

Ready to optimize your Oracle Database for the future?