Parallelizing Index Creation- Tables and Constraints

In large database environments in which you are attempting to create an index on a table that is populated with many rows, you may be able to greatly increase the index creation speed by using the PARALLEL clause:

SQL> create index cust_idx1 on cust(cust_id) parallel 2

tablespace reporting_index;

If you do not specify a degree of parallelism, Oracle selects a degree, based on the number set in the CPU_COUNT parameter and set in the value of PARALLEL_THREADS_ PER_CPU.

You can run this query to verify the degree of parallelism associated with an index:

SQL> select index_name, degree from user_indexes;

You can also disable parallelism as execution plans might show unwanted parallelism:

SQL> alter index cust_idx1 noparallel;

Avoiding Redo Generation When Creating an Index

You can optionally create an index with the NOLOGGING clause. Doing so has these implications:

•     The redo is not generated that would be required to recover the index in the event of a media failure.

•     Subsequent direct-path operations also will not generate the redo required to recover the index information in the event of a media failure.

Here is an example of creating an index with the NOLOGGING clause:

SQL> create index cust_idx1 on cust(cust_id) nologging

tablespace users;

The main advantage of NOLOGGING is that when you create the index, a minimal amount of redo information is generated, which can have significant performance implications for a large index. The disadvantage is that if you experience a media failure soon after the index is created (or have records inserted via a direct-path operation) and you restore and recover the database from a backup that was taken prior to the index creation, you will see this error when the index is accessed:

ORA-01578: ORACLE data block corrupted (file # 4, block # 1044) ORA-01110: data file 4: ‘/u01/dbfile/O18C/users01.dbf’

ORA-26040: Data block was loaded using the NOLOGGING option

This error indicates that the index is logically corrupt. In this scenario, you must rebuild the index before it is usable. In most scenarios, it is acceptable to use the NOLOGGING clause when creating an index, because the index can be re-created without affecting the table on which the index is based.

You can run this query to view whether an index has been created with NOLOGGING:

SQL> select index_name, logging from user_indexes;

Implementing Invisible Indexes

As discussed in creating an index on the same columns with only one visible, you have the option of making an index invisible to the optimizer. Oracle still maintains an invisible index (as DML occurs on the table) but does not make it available for use by the optimizer. You can use the OPTIMIZER_USE_INVISIBLE_INDEXES database parameter to make an invisible index visible to the optimizer.

Invisible indexes have a couple of interesting uses:

•     Altering an index to be invisible before dropping it allows you to quickly recover if you later determine that the index is required.

•     You may be able to add an invisible index to a third-party application without affecting existing code or support agreements.

These two scenarios are discussed in the following sections.