Ittichai at 00:55, 15 March 2011

2011-03-15T00:55:58Z

Ittichai at 00:53, 15 March 2011

2011-03-15T00:53:47Z

Ittichai: 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.

..."

2011-03-15T00:53:24Z

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>..."

New page

== 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>
SQL> create index ENAME_IDX on EMP(ENAME);
Index created
</pre>

* By default, the index is VISIBLE. To create an INVISIBLE index directly, use create index ENAME_IDX on EMP(ENAME) INVISIBLE;
<pre>
SQL> select index_name, VISIBILITY from user_indexes where index_name='ENAME_IDX';

INDEX_NAME VISIBILITY
------------------------------ ----------
ENAME_IDX VISIBLE
</pre>

* Explain plan: expected INDEX will be used.
<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 | 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')
</pre>

* Make index INVISIBLE. The Optimizer will IGNORE this index.
<pre>
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
</pre>

* Explain plan: expected index will NOT be used.
<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: 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')
</pre>

* Oracle has the initialization parameter '''OPTIMIZER_USE_INVISIBLE_INDEXES'''. Its default is FALSE. This parameter can be enabled at the SESSION level.
<pre>
SQL> show parameter optimizer_use_invisible_indexes

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_invisible_indexes boolean FALSE
</pre>

* Enable the use of index even though it is INVISIBLE.
<pre>
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')

</pre>

[[Category:Database_Features]]
[[Category:Oracle_11g]]

Invisible Index - Revision history

Ittichai at 00:55, 15 March 2011

Ittichai at 00:53, 15 March 2011

Ittichai: 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.
..."

@@ Line 89: / Line 89: @@
 Session altered
-<pre>
+</pre>
 * Explain Plan: now expected index will be used even though it is INVISIBLE.

@@ Line 1: / Line 1: @@
 == What is Invisible Index? ==
 * Invisible indexes will be ignored by Optimizer.
-* Help to see whether index should be dropped before drop
+* Help to see whether index should be dropped before actually dropping it.
 == Demo ==