Indexes - Pro Oracle Database 12c Administration - page 223

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

SQL> select index_name, degree from user_indexes;

Avoiding Redo Generation When Creating an Index
You can optionally create an index with the NOLOGGING clause. Doing so has these implications:

• The redo isn't generated that would be required to recover the index in the event of a media failure.

• Subsequent direct-path operations also won't 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:

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'll 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/O12C/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 recreate the index before it's
usable. In most scenarios it's acceptable to use the NOLOGGING clause when creating an index, because the index can
be recreated 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
In Oracle Database 11g and higher, 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 doesn't 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 can 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.