PARALLEL_INDEX and NO_PARALLEL_INDEX Hints

Introduction and Warning

Before discussing how to use the parallel hints, it is important to note, that these hints should be used sparingly, with caution and only when your system has enough IO and CPU resources to do so.   It is exceptionally easy to bring a system to its knees using these hints.  If you plan to use these hints, heed the warning and test before implementation.  That said, lets take a look at how to use them.

PARALLEL_INDEX Hint Description

The PARALLEL_INDEX hint is similar to the PARALLEL hint but is specifically used to parallelize index-related operations, such as creating or scanning indexes. While the PARALLEL hint applies to the entire query or DML operation (like select, insert, update, or delete), the PARALLEL_INDEX hint targets only index operations, typically improving performance for tasks like index range scans, full scans, fast full scans or index creation. 

Example

SELECT /*+ PARALLEL_INDEX(myTab1, myInd1, degree) */ col1 

  FROM myTab1

/

NO_PARALLEL_INDEX Hint Description

The NO_PARALLEL_INDEX hint explicitly disables parallel execution for a query, overriding any parallelism settings that may be configured at the system, session, or object level. 

Example

SELECT /*+ NO_PARALLEL_INDEX(myTab1, myInd1) */ col1 

  FROM myTab1

/

Notes

• You can find more information on using parallel query in the documentation

Published on 

Published 10th January 2025