Oracle 11g Invisible Indexes

Summary

Oracle has now provided in what is my opinion a huge step forward in query tuning. It will now allow us to hide indexes from the optimizer without have to drop them. If you have every tuned queries of very large tables and had to drop and recreate indexes in your query tuning testing then you will realize right away how useful this is.

Another huge advantage and use of this feature is for batch process tuning. Ever had a situation where you needed an index that would really help your batch process but hurt OLTP transaction query performance. Well now we can hide the index from OLTP, but allow our batch processes to see it.

To use an invisible index you can set the parameter optimizer_use_invisible_indexes to true. The default of this parameter is false and can be set via an alter session or alter system command. Otherwise invisible indexes are completely ignore by the optimizer including through hints.

A special thing to note about invisible indexes is that they can not get optimizer statistics gathered on them while in an invisible state. Therefore to maintain statistics that index will have to be made visible by altering the index or setting the optimizer_use_invisible_indexes to true.


Example

1. Create a table for use in our example:

SQL> create table my_table as select * from scott.emp ;

Table created.


2. Create an index on our table and make it invisible.

SQL> create index my_index on my_table (ename) invisible ;

Index created.


3. Check that the index is invisible using the new VISIBILITY column of the user_indexes, all_indexes or dba_indexes views.

SQL> select index_name, visibility from user_indexes where index_name = 'MY_INDEX';

INDEX_NAME VISIBILIT
------------------------------ ---------
MY_INDEX INVISIBLE

1 row selected.


4. Attempt to gather statistics on our new and cascading to indexes. Not since the index is invisible this fails.

SQL> execute dbms_stats.gather_table_stats ('MRMESSIN', 'MY_TABLE') ;
BEGIN dbms_stats.gather_table_stats ('MRMESSIN', 'MY_TABLE') ; END;

*
ERROR at line 1:
ORA-00904: : invalid identifier
ORA-06512: at "SYS.DBMS_STATS", line 17806
ORA-06512: at "SYS.DBMS_STATS", line 17827
ORA-06512: at line 1


4. Make the invisible indexes visible.

SQL> alter session set optimizer_use_invisible_indexes = true ;

Session altered.


5. Now gather our statistics

SQL> execute dbms_stats.gather_table_stats ('MRMESSIN', 'MY_TABLE') ;

PL/SQL procedure successfully completed.


6. Set the optimizer_use_invisible_indexes back to false for the start of our testing.

SQL> alter session set optimizer_use_invisible_indexes = true ;


7. Set autotrace so we can see our explain plan and data.

SQL> set autotrace traceonly


8. Select from the table using the indexed column in our where clause which under normal circumstances that optimizer would choose to use the index. Note however we are not using the index because it is invisible.

SQL> select empno, ename from my_table where ename = 'BLAKE';

1 row selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3804444429

----------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
----------------------------------------------------------------------------
0 SELECT STATEMENT 1 10 3 (0) 00:00:01
* 1 TABLE ACCESS FULL MY_TABLE 1 10 3 (0) 00:00:01
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("ENAME"='BLAKE')


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
477 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed


9. Now lets query seeing if we can use the index Hint to use the index. Not the index is not used.

SQL> select /*+ INDEX(my_table my_index) */ empno, ename from my_table where ename = 'BLAKE’ ;

1 row selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3804444429

----------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
----------------------------------------------------------------------------
0 SELECT STATEMENT 1 10 3 (0) 00:00:01
* 1 TABLE ACCESS FULL MY_TABLE 1 10 3 (0) 00:00:01
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("ENAME"='BLAKE')


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
477 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed


10. Now let see if the index is used if we set the parameter optimizer_use_invisible_indexes to true.

SQL> alter session set optimizer_use_invisible_indexes = true ;

Session altered.


11. Note that index is now seen and is being used.

SQL> select empno, ename from my_table where ename = 'BLAKE';

1 row selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 332163487

-----------------------------------------------------------------------------
Id Operation Name RowsBytesCost %CPU) Time
-----------------------------------------------------------------------------
0 SELECT STATEMENT 1 10 2 (0) 00:00:01
1 TABLE ACCESS BY INDEX ROWIDMY_TABLE 1 10 2 (0) 00:00:01
*2 INDEX RANGE SCAN MY_INDEX 1 1 (0) 00:00:01
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ENAME"='BLAKE')


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
477 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed