Databases Reference
In-Depth Information
You can parallelize an index access by altering an index with the
PARALLEL
clause, as shown here:
SQL> alter index testtab_idx1 parallel;
Index altered.
SQL>
Since you didn't specify the degree of parallelism, Oracle Database uses the default degree of
parallelism, as shown here:
SQL>select degree from user_indexes where index_name='TESTTAB_IDX1';
DEGREE
----------------------------------------
DEFAULT
SQL>
You can specify a non-default degree of parallelism by specifying the degree as follows:
SQL> alter index testtab_idx1 parallel 12;
Index altered.
SQL>
You can specify the degree of parallelism for an index when you create one, as shown in the
following example:
SQL> create index testtab_idx2 on testtab(x)
2 parallel (degree 8);
Index created.
SQL>
You can disable a parallel degree setting by doing the following:
SQL>alter index mtab_idx noparallel;
You can also specify the parallel degree when you rebuild an index.
Summary
This chapter started off with a discussion of the various types of index access paths, such as a fast full
scan and an index range scan, and their implications. You also learned under what conditions the
optimizer might select various index access paths. On occasion, it may better to force a full table scan for
some queries and this chapter showed you how to do that. This chapter discussed several reasons why
the optimizer may not choose to use an index and how you can use various strategies to influence the
optimizer's choice of an index. Finally, this chapter showed you how to parallelize index access and how
to specify the parallel option when creating an index.
