Expert Indexing in Oracle Database 11g: Maximum Performance for Your Database

Parallelizing Index Access

When dealing with partitioned indexes, you can direct the optimizer to use multiple concurrent parallel servers to parallelize several types of index operations. The operations you can parallelize include index range scans, full index scans, and fast full scans.

Here's an example that shows how you can specify the PARALLEL_INDEX hint to specify a parallel scan operation on a partitioned index:

SQL> select /*+parallel_index (employees, employee_id_idx, 4) */ last_name,      employee_id      from employees;

The integer 4 specifies the degree of parallelism for the index scan.

There is also a NO_PARALLEL_INDEX hint that overrides the degree of parallelism you specified for an index. Note that the PARALLEL_INDEX as well ...