CREATING AN INVISIBLE INDEX

posted Jan 28, 2012, 2:11 PM by Sachchida Ojha   [ updated Jan 28, 2012, 4:58 PM ]
WHEN YOU CREATE ANY INDEX, BY DEFAULT, IT IS VISIBLE TO THE OPTIMIZER AND IT STARTS TAKING INTO ACCOUNT WHEN CALCULATING COST ESTIMATIONS. HOWEVER THERE ARE OCCASIONS WHEN WE WANT TO CREATE AN INDEX BUT CONTROL WHEN THE OPTIMIZER CAN USE THE INDEX. WE CAN HIDE AN INDEX FROM THE OPTIMIZER BY CREATING AN INDEX AS AN "INVISIBLE" INDEX. WE CAN ALSOO ALTER THE STATUS OF AN EXISTING INDEX BY MAKING IT INVISIBLE.

SQL> CREATE INDEX test_idx1 on employees(salary) invisible;

Index created.

You can make an existing index VISIBLE/invisible by using following alter index command.

SQL> ALTER INDEX test_idx1 visible;

Index altered.

SQL> ALTER INDEX test_idx1 invisible;

Index altered.

To see all invisible indexes in your database,

SQL> select index_name, visibility  from dba_indexes where visibility='INVISIBLE';


Once you make an index invisible optimizer does not use that index. Even an index hint won't make the optimizer see an invisible index. However you can make an invisible index available to optimizer at the session level or the system level by setting the OPTIMIZER_USE_INVISIBLE_INDEXES parameter to true.







Comments