When creating or re-creating an index, you can use the following keywords
to reduce the creation time:
•
PARALLEL
(NOPARALLEL is the default): Multiple processes can work
together simultaneously to create an index. By dividing the work necessary to
create an index among multiple server processes, the Oracle server can create
the index more quickly than if a single server process created the index
sequentially. The table is randomly sampled and a set of index keys is found
that equally divides the index into the same number of pieces as the specified
degree of parallelism. A first set of query processes scans the table, extracts
key, row ID pairs, and sends each pair to a process in a second set of query
processes based on the key. Each process in the second set sorts the keys and
builds an index in the usual fashion. After all index pieces are built, the
parallel coordinator concatenates the pieces (which are ordered) to form the
final index.
SQL>
CREATE INDEX rname_idx
2 ON
hr.regions (region_name)
3
PARALLEL 4;
•
NOLOGGING: Using
this keyword makes index creation faster because it creates a very minimal
amount of redo log entries as a result of the creation process. This greatly
minimized redo generation also applies to direct path inserts and Direct Loader
(SQL*Loader) inserts. This is a permanent attribute and thus appears in the
data dictionary. It can be updated with the ALTER INDEX NOLOGGING/LOGGING
command at any time.
•
When an index is lost, it may be faster and simpler just to re-create it
rather than attempt to recover it. One way to determine the SQL for creating
the index is by using the impdp SQLFILE=<filename> command on a
previously generated expdp output file. This generates the SQL statements
needed to create the objects in the dump file.
Recovering
from a List Index Tablespace
Indexes are
computed objects, in that they do not provide any original data, and they are
only a different representation of data that already exists. So, in most cases,
indexes can be re-created easily. If you have a tablespace that contains only
indexes, recovering from a loss of a data file belonging to that tablespace can
be simplified.
When a data
file like this is lost, you can perform the following steps:
1. Drop the data file.
2. Drop the tablespace.
3. Re-create the index tablespace.
4. Re-create the indexes that were in the
tablespace.
