Databases Reference
In-Depth Information
Parallelizing Index Creation
Using the
parallel
option during index creation will help speed up the creation of a large index. In order
to create an index, the database needs to perform a full table scan. Specifying the
parallel
clause makes
the database perform the full table scan in parallel, thus making the index creation finish much faster.
There is no hard and fast rule, of course, as to the appropriate degree of parallelism; it depends on the
number of CPUs on your system. Here's an example that shows how to specify the
parallel
option:
SQL> create index text_idx1
on employees (last_name,first_name)
parallel 12;
You can also specify the
parallel
option when rebuilding an index, as shown here:
SQL> alter index text_idx1 rebuild parallel 12;
Index altered.
SQL>
Specifying parallelism during the creation or rebuilding of an index will definitely make the index
creation/rebuild process finish much faster than otherwise, provided you have the necessary I/O
bandwidth and CPU resources to handle the demands of the parallel processes. A word of caution,
however: the parallelism you specify during the creation or rebuilding of an index doesn't end there!
Such a parallel operation will
persist
the parallelism degree of the index, as can be shown by the
following query.
SQL> select index_name,degree from user_indexes where degree > 1;
INDEX_NAME DEGREE
------------------------------ ----------------------------------------
TEXT_IDX1 12
SQL>
What this query's output is telling you is that even though you had merely intended to speed up
your index creation or rebuild with the parallel option (
parallel 12
), the database has permanently
modified the parallelism degree of the index
text_index1
to 12 from its default value of 1. Any query
operations involving that index will begin defaulting to parallel execution. This is not always a good
thing!
By the way, the same is also true with tables. If you specify the
parallel
option during an
alter
table …move
or a
create table … as
operation, the database will
permanently
modify the existing
parallelism of the table (default is 1) to the degree you specify for the operation on the table.
Tip
Creating or rebuilding an index in parallel will change the degree of parallelism for that index. The optimizer
will take into account this fact when it calculates the cost of alternative execution paths.
