Oracle 11g Invisible Indexes

Invisible Indexes ( Oracle 11g New Features)

One can create Invisible Indexes to test performance or convert existing index into Invisible Indexe to check impact.
Optimizer not consider invisible indexes , we need to specify explicity with hint.Making an index invisible is an alternative to making it unusable or dropping it. Unlike unusable indexes, invisible indexes are maintained during DML statements.


CREATE INDEX I_emp_ename ON emp(ename)
TABLESPACE users
STORAGE (INITIAL 10K NEXT 10k PCTINCREASE 50)
INVISIBLE;

ALTER INDEX I_emp_ename INVISIBLE;

ALTER INDEX I_emp_ename VISIBLE;


SELECT INDEX_NAME, VISIBILITY FROM USER_INDEXES
WHERE INDEX_NAME = 'I_EMP_ENAME';

INDEX_NAME VISIBILITY
---------- ----------
I_EMP_ENAME VISIBLE