Difference between revisions of "Invisible Index"
Jump to navigation
Jump to search
Line 89: | Line 89: | ||
Session altered | Session altered | ||
− | <pre> | + | </pre> |
* Explain Plan: now expected index will be used even though it is INVISIBLE. | * Explain Plan: now expected index will be used even though it is INVISIBLE. |
Latest revision as of 19:55, 14 March 2011
What is Invisible Index?
- Invisible indexes will be ignored by Optimizer.
- Help to see whether index should be dropped before actually dropping it.
Demo
- Create a new index.
SQL> create index ENAME_IDX on EMP(ENAME); Index created
- By default, the index is VISIBLE. To create an INVISIBLE index directly, use create index ENAME_IDX on EMP(ENAME) INVISIBLE;
SQL> select index_name, VISIBILITY from user_indexes where index_name='ENAME_IDX'; INDEX_NAME VISIBILITY ------------------------------ ---------- ENAME_IDX VISIBLE
- Explain plan: expected INDEX will be used.
SQL> explain plan for select ENAME from EMP where ENAME='MILLER'; Explained SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 4091927879 ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 7 | 1 (0)| 00:00:01 | |* 1 | INDEX RANGE SCAN| ENAME_IDX | 1 | 7 | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("ENAME"='MILLER')
- Make index INVISIBLE. The Optimizer will IGNORE this index.
SQL> alter index ENAME_IDX invisible; Index altered SQL> select index_name, VISIBILITY from user_indexes where index_name='ENAME_IDX'; INDEX_NAME VISIBILITY ------------------------------ ---------- ENAME_IDX INVISIBLE
- Explain plan: expected index will NOT be used.
SQL> explain plan for select ENAME from EMP where ENAME='MILLER'; Explained SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 2872589290 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 6 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| EMP | 1 | 6 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("ENAME"='MILLER')
- Oracle has the initialization parameter OPTIMIZER_USE_INVISIBLE_INDEXES. Its default is FALSE. This parameter can be enabled at the SESSION level.
SQL> show parameter optimizer_use_invisible_indexes NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ optimizer_use_invisible_indexes boolean FALSE
- Enable the use of index even though it is INVISIBLE.
SQL> alter session set optimizer_use_invisible_indexes=TRUE; Session altered
- Explain Plan: now expected index will be used even though it is INVISIBLE.
SQL> explain plan for select ENAME from EMP where ENAME='MILLER'; Explained SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 4091927879 ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 6 | 1 (0)| 00:00:01 | |* 1 | INDEX RANGE SCAN| ENAME_IDX | 1 | 6 | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("ENAME"='MILLER')