Suppose you have identified an index that is not being used and are considering dropping it. In earlier releases of Oracle, you could mark the index UNUSABLE and then later drop indexes that you were certain weren’t being used. If you later determined that you needed an unusable index, the only way to re-enable the index was to rebuild it. For large indexes, this could take a great amount of time and database resources.

Making an index invisible has the advantage of telling only the optimizer not to use the index. The invisible index is still maintained as the underlying table has records inserted, updated, and deleted. If you decide that you later need the index, there is no need to rebuild it; you simply make it visible again.

You can create an index as invisible or alter an existing index to be invisible; for example,

SQL> create index cust_idx2 on cust(first_name) invisible; SQL> alter index cust_idx1 invisible;

You can verify the visibility of an index via this query:

SQL> select index_name, status, visibility from user_indexes;

Here is some sample output:

INDEX_NAME

——————–CUST_IDX1

CUST_IDX2

USERS_IDX1

STATUS

——–VALID VALID

VALID

VISIBILITY

———-INVISIBLE INVISIBLE

VISIBLE

Use the VISIBLE clause to make an invisible index visible to the optimizer again:

SQL> alter index cust_idx1 visible;

Note If you have a B-tree index on a foreign key column and you decide to make it invisible, Oracle can still use the index to prevent certain locking issues. Before you drop an index on a column associated with a foreign key constraint, ensure that it is not used by Oracle to prevent locking issues. See the section “Indexing Foreign Key Columns,” later in this chapter, for details.

Guaranteeing Application Behavior Is Unchanged When You Add an Index

You can also use an invisible index when you are working with third-party applications. Often, third-party vendors do not support customers adding their own indexes to an application. However, there may be a scenario in which you are certain you can increase a query’s performance without affecting other queries in the application.

You can create the index as invisible and then use the OPTIMIZER_USE_INVISIBLE_ INDEXES parameter to instruct the optimizer to consider invisible indexes. This parameter can be set at the system or session level. Here is an example:

SQL> create index cust_idx1 on cust(cust_id) invisible;

Now, set the OPTIMIZER_USE_INVISIBLE_INDEXES database parameter to TRUE. This instructs the optimizer to consider invisible indexes for the currently connected session:

SQL> alter session set optimizer_use_invisible_indexes=true;

You can verify that the index is being used by setting AUTOTRACE to on and running the SELECT statement:

SQL> set autotrace trace explain;

SQL> select cust_id from cust where cust_id = 3;

Here is some sample output, indicating that the optimizer chose to use the invisible index:

—————————————————————————| Id | Operation               | Name           | Rows | Bytes | Cost (%CPU)| Time —-| —————————————————————————| 0 | SELECT STATEMENT |                    | 1        | 5          | 1 (0)           | 00:00:01 | |* 1 | INDEX RANGE SCAN| CUST_IDX1 | 1                      | 5                                          | 1 (0)         | 00:00:01 | —————————————————————————

Keep in mind that invisible index simply means an index the optimizer cannot see. Just like any other index, an invisible index consumes space and resources during DML statements.