Parallel Index Creation

Parallel index creation works in much the same way as parallel execution of queries. One set of query servers scans the table to be indexed to obtain the rowids and column values for the rows. Then another set of query servers performs the sorting of the index entries based on the index column values and passes off the sorted entries to the coordinator process, which builds the B*-tree index from the sorted lists.

You can optionally specify that the creation of an index be unrecoverable. This means that no redo logging occurs during the index creation. This can significantly improve performance, but it sacrifices recoverability of the index in the event of media failure. If recoverability is not important to your application, you should consider using the UNRECOVERABLE option. For more information on recovery and the UNRECOVERABLE option, see the Oracle7 Server Administrator's Guide.

By default, the Oracle Server uses the table definition's PARALLEL clause value to determine the number of server processes to use when creating an index. You can override the default number of processes by using the PARALLEL clause in the CREATE INDEX command. Refer to the Oracle7 Server SQL Reference for the complete syntax of the CREATE INDEX command.

Attention: When creating an index in parallel, the STORAGE clause refers to the storage of each of the subindexes created by the query server processes. Therefore, an index created with an INITIAL of 5M and a PARALLEL DEGREE of 12 consumes at least 60M of storage during index creation because each process starts with an extent of 5M. When the query coordinator process combines the sorted subindexes, some of the extents may be trimmed, and the resulting index may be smaller than the requested 60M.

When you add or enable a UNIQUE key or PRIMARY KEY constraint on a table, you cannot automatically create the required index in parallel. Instead, manually create an index on the desired columns using the CREATE INDEX command and an appropriate PARALLEL clause and then add or enable the constraint. Oracle then uses the existing index when enabling or adding the constraint.

Refer to "Tuning for the Parallel Query Option" for advice on tuning your system when creating indexes in parallel. For more information on how extents are allocated when using the parallel query option, see Oracle7 Server Concepts.