Difference between revisions of "Invisible Index"
Jump to navigation
Jump to search
(Created page with "== What is Invisible Index? == * Invisible indexes will be ignored by Optimizer. * Help to see whether index should be dropped before drop == Demo == * Create a new index. <pre>...") |
|||
Line 1: | Line 1: | ||
== What is Invisible Index? == | == What is Invisible Index? == | ||
* Invisible indexes will be ignored by Optimizer. | * Invisible indexes will be ignored by Optimizer. | ||
− | * Help to see whether index should be dropped before | + | * Help to see whether index should be dropped before actually dropping it. |
== Demo == | == Demo == |
Revision as of 19:53, 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 <pre> * Explain Plan: now expected index will be used even though it is INVISIBLE. <pre> 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')