Specialized Indexes - Expert Indexing in Oracle Database 11g - page 84

And you can toggle the status of the index back to visible by doing this:
SQL> alter index test_idx visible;
Index altered.
SQL>
Finding Invisible Indexes In Your Database
You can check the visibility status of an index by executing the following query:
SQL> select index_name, visibility from dba_indexes
2* where visibility='INVISIBLE';
INDEX_NAME VISIBILIT
------------------------------ ---------
TEST_IDX1 INVISIBLE
SQL>
This query helps you easily keep track of all invisible indexes in your database. You should know about
them because the database engine does need to maintain them. They are invisible, not free of cost.
Making an Invisible Index Available to the Optimizer
Once you make an index invisible, the optimizer doesn't use that index. Even specifying the INDEX hint
won't make the optimizer see an invisible index. However, you can make an invisible index available to
the optimizer at the session level or the system level by setting the optimizer_use_invisible_indexes
parameter to true . The optimizer_use_invisble_indexes parameter controls the use of an invisible
index. When you set this parameter to true , an invisible index is considered the same as a normal, visible
index. If you set this parameter to false (the default value), the optimizer ignores the invisible index. By
default, the optimizer_use_invisble_indexes initialization parameter is set to false , as shown here:
SQL> show parameter invisible
NAME TYPE VALUE
---------------------------------------- ----------- -------------
optimizer_use_invisible_indexes boolean FALSE
SQL>
If you have an invisible index on a column, the optimizer by default won't use that index. You can
confirm this by running an explain plan for a SQL statement that involves the index. You'll notice that
the database does a full table scan instead of using the invisible index.
You can make an invisible index available to the optimizer by using an index hint in a query. But
first you must set the optimizer_use_invisible_indexes parameter to true at the session or system level
before you can specify the INDEX hint.