Database Reference
In-Depth Information
Invisible Indexes
From version 11.1 onward, there's an optional index attribute that specifies whether an index is visible to the query
optimizer. By default, indexes are visible. In case an index is invisible, it's regularly maintained when the data of the
table it's based on is modified, but the query optimizer can't take advantage of it during the generation of execution
plans. Because invisible indexes are regularly maintained, constraints based on a unique index are still regularly
enforced even if the index they're based on is invisible.
■
In version 11.1 the invisibility of an index isn't total. In fact, the query optimizer takes advantage of invisible
indexes in two unexpected situations. First, even though an invisible index isn't included in the execution plan, the query
optimizer can use the statistics associated to it to improve the estimations it makes. the
invisible_index_stats.sql
script demonstrates such a case. Second, the database engine can take advantage of an invisible index to avoid false
contention due to unindexed foreign keys.
Caution
The following example, based on the
invisible_index.sql
script, shows how to make an index invisible and the
impact of such an operation for a specific query:
SQL> SELECT * FROM t WHERE id = 42;
--------------------------------------------
| Id | Operation | Name |
--------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| T |
|* 2 | INDEX UNIQUE SCAN | T_PK |
--------------------------------------------
2 - access("ID"=42)
SQL> SELECT visibility FROM user_indexes WHERE index_name = 'T_PK';
VISIBILITY
----------
VISIBLE
SQL> ALTER INDEX t_pk INVISIBLE;
SQL> SELECT visibility FROM user_indexes WHERE index_name = 'T_PK';
VISIBILITY
----------
INVISIBLE
SQL> SELECT * FROM t WHERE id = 42;
