Invisible Indexes-Indexes

You have the option of making an index invisible to the optimizer. The index is only invisible in the sense that the optimizer won’t use the index when creating an execution plan. You can either create an index as invisible or alter an existing index to be invisible. Here, we create a table, load it with test data, generate statistics, and then create an invisible index:
$ sqlplus eoda/foo@PDB1
SQL> create table t(x int);Table created.
SQL> insert into t select round(dbms_random.value(1,10000)) from dual connect by level <=10000; SQL> exec dbms_stats.gather_table_stats(user,’T’); PL/SQL procedure successfully completed.
SQL> create index ti on t(x) invisible; Index created.

Now we turn on autotrace and run a query where one would expect the optimizer to use the index when generating an execution plan:
SQL> set autotrace traceonly explain
SQL> select * from t where x=5;

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:
SQL> alter session set optimizer_use_invisible_indexes=true;
Rerunning the prior query shows the optimizer now takes advantage of the index:
SQL> select * from t where x=5;

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:
SQL> 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 underlyingtable has records inserted, updated, or deleted. This could impactperformance (slow down DML statements).
•\ Oracle can still use an invisible index to prevent certain locking ituations when a B*Tree index is placed on a foreign key column.
•\ If you create a unique invisible index, the uniqueness of the columns ill 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.