Index Enhancements   «Prev 

Rebuilding and defragmenting indexes online


CREATE INDEX index_name ON table_name

CREATE INDEX   Required keywords.
index_name   Unique name for the index.
ON   Required keyword.
table_name   Unique table name.
column_list   List of columns that make up the index.
ONLINE   Required keyword to build index while leaving underlying table available.

Oracle 12c Performance Tuning

Invisible Index

Making an index invisible requires Oracle Database 11g. An invisible index is still maintained by Oracle but is not considered by the query optimizer when determining the execution plan. Be aware that an invisible index may still be used internally by Oracle to prevent locking issues or to enforce constraints. So just making an index invisible is not a completely reliable way to determine if it is used. Here is an example of making an index invisible:

SQL> alter index addr_fk1 invisible;

This code makes the index invisible to the query optimizer so that it canot be used to retrieve rows for a query. However, the index structure is still maintained by Oracle as records are modified in the table. If you determine that the index was critical for performance, you can easily make it visible to the optimizer again by means of
SQL> alter index addr_fk1 visible;

Your other option before dropping an index is to make it unusable.
SQL> alter index addr_fk1 unusable;

This code renders the index unusable, but does not drop it.
Unusable means that the optimizer will not use the index and Oracle will not maintain the index as DML statements operate on its table. Furthermore, an unusable index canot be used internally to enforce constraints or prevent locking issues.