Databases Reference
In-Depth Information
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 as the
NO_PARALLEL_INDEX
hints are object level hints that
have been superseded by the statement level
PARALLEL
and
NOPARALLEL
hints in Oracle Database 11g
Release 2. You can specify the degree of parallelism for a statement level
PARALLEL
hint in various ways,
as explained through the following examples. Note that if you don't specify the degree of parallelism, the
optimizer calculates its own degree of parallelism:
SQL>select /*+ parallel */ last_name from employees;
If you specify the
PARALLEL (AUTO)
hint, the database calculates the degree of parallelism, which
could end up being just 1 (serial execution).
SQL> select /*+ PARALLEL (AUTO) */ last_name from employees;
If you specify the
PARALLEL (MANUAL)
hint, the optimizer uses the degree of parallelism in force for
the object. If you specify
PARALLEL (integer),
the optimizer uses the degree of parallelism you specify. If
you're using the
PARALLEL_INDEX
hint, the database will not adjust the cost of a parallel index full scan by
the degree of parallelism you specify unless you've also declared the table as parallel.
You can execute DDL statements in parallel for both partitioned and nonpartitioned indexes. For a
partitioned index, the parallel DDL statements can be used for the following operations:
CREATE INDEX
ALTER INDEX …[REBUILD|SPLIT] PARTITION
By default, Oracle Database uses a degree of parallelism of 1, as you can see from the following
example:
SQL> create table testtab (x int, y int);
Table created.
SQL> create index testtab_idx1 on testtab(x,y);
Index created.
SQL> select degree from user_indexes where index_name='TESTTAB_IDX1';
DEGREE
----------------------------------------
1
SQL>
