Database Reference
In-Depth Information
The prior output shows that the index wasn't used by the optimizer. You can toggle an index's visibility to the
optimizer during a session by setting the
OPTIMIZER_USE_INVISIBLE_INDEXES
initialization parameter to
TRUE
(the default is
FALSE
). For example, for the currently connected session, the following instructs the optimizer to
consider invisible indexes when generating an execution plan:
EODA@ORA12CR1> alter session set optimizer_use_invisible_indexes=true;
Rerunning the prior query shows the optimizer now takes advantage of the index:
EODA@ORA12CR1> select * from t where x=5;
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 8 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| TI | 2 | 8 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------
If you want all sessions to consider using invisible indexes, then alter the
OPTIMIZER_USE_INVISIBLE_INDEXES
parameter via the
ALTER SYSTEM
statement. This makes all invisible indexes visible to the optimizer when generating
execution plans.
You can make an index permanently visible to the optimizer by altering it to visible:
EODA@ORA12CR1> alter index ti visible;
Index altered.
Keep in mind that although an invisible index may be invisible to the optimizer, it can still impact performance in
the following ways:
•
Invisible indexes consume space and resources as the underlying table has records inserted,
updated, or deleted. This could impact performance (slow down DML statements).
•
Oracle can still use an invisible index to prevent certain locking situations when a B*Tree
index is placed on a foreign key column.
•
If you create a unique invisible index, the uniqueness of the columns will be enforced
regardless of the visibility setting.
Therefore, even if you create an index as invisible, it can still influence the behavior of SQL statements. It would
be erroneous to assume that an invisible index has no impact on the applications using the tables on which invisible
indexes exist. Invisible indexes are only invisible in the sense that the optimizer won't consider them for use when
generating execution plans unless instructed to do so.
So what is the usefulness of invisible indexes? These indexes still have to be maintained (hence slowing
performance), but cannot be used by queries that can't see them (hence never boosting performance). One
example would be if you wanted to drop an index from a production system. The idea being you could make the
index invisible and see if performance suffered. In this case, before dropping the index, you'd also have to be
careful that the index wasn't placed on a foreign key column or wasn't being used to enforce uniqueness. Another
example would be where you wanted to add an index to a production system and test it to determine if performance
improved. You could add the index as invisible and selectively make it visible during a session to determine its
usefulness. Here again, you'd also have to be mindful that even though the index is invisible, it will consume space
and require resources to maintain.
