This article will be discussing a new feature of Oracle 11g, which is invisible indexes. Invisible indexes simply means indexes that are not visible to optimizer, meaning the optimizer will not consider such index while preparing query execution plans. It is possible to force the optimizer to consider invisible indexes, but let’s cover the benefits of invisible indexes first:
Benefits
- Test the benefit of an index: Every DML statement will have an extra cost to maintain an index. To ensure that an index is actually useful for our system before dropping it, we can test it by simply making it invisible. The index will not be considered by optimizer anymore. If SQL statements that were using this index perform acceptably, then we should drop the index.
- Introduce New Index: We can introduce a new index without effecting execution plans of existing sql statements.
Examples
Lets create a table and populate it with some data and then create an invisible index on it.
SQL> CREATE TABLE index_test(
2 number Number(10),
3 avarchar2 Varchar2(100),
4 adate Date);
Table created.
SQL> BEGIN
2 FOR x IN 1..1000 LOOP
3 INSERT INTO index_test
4 VALUES (x, RPAD('0',100,'0'), SYSDATE);
5 END LOOP;
6 END;
7 /
PL/SQL procedure successfully completed.
SQL> COMMIT;
Commit complete.
SQL> CREATE INDEX idx_invisible ON index_test (anumber) INVISIBLE;
Index created.
SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'Index_Test', cascade => TRUE);
PL/SQL procedure successfully completed.We have created “idx_invisible” index and marked it as “invisible”, now lets verify it from data dictionary and from sql statement execution plan.
SQL> SELECT index_name, visibility
2 FROM user_indexes
3 WHERE index_name = 'IDX_INVISIBLE';
INDEX_NAME VISIBILIT
------------------------------ ---------
IDX_INVISIBLE INVISIBLE
SQL> SET AUTOTRACE ON
SQL> SELECT adate
2 FROM index_test
3 WHERE anumber = 567;
ADATE
---------
24-OCT-12
-----------------------------------------------------------------------------
| Id | Operation | Name |Rows | Bytes |Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 1 | 113 | 7 (0)| 00:00:01 |
|* 1| TABLE ACCESS FULL| INDEX_TEST | 1 | 113 | 7 (0)| 00:00:01 |
-----------------------------------------------------------------------------
We can verify that optimizer is doing “TABLE ACCESS FULL” on table “INDEX_TEST”. Although we are querying table “INDEX_TEST” on column “ANUMBER” which has index, that index is invisible to optimizer. Let’s suggest the optimizer to use index “IDX_INVISIBLE” with a hint.
SQL> SELECT /*+ INDEX(Index_Test idx_invisible) */ adate
2 FROM index_test
3 WHERE anumber = 567;
ADATE
---------
24-OCT-12
-----------------------------------------------------------------------------
| Id | Operation | Name |Rows | Bytes |Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 1 | 113 | 7 (0)| 00:00:01 |
|* 1| TABLE ACCESS FULL| INDEX_TEST | 1 | 113 | 7 (0)| 00:00:01 |
-----------------------------------------------------------------------------Same execution plan, so now we are sure that optimizer will never use an invisible index. So lets make our “IDX_INVISIBLE” visible to optimizer and verify from data dictionary and optimizer.
SQL> ALTER INDEX idx_invisible VISIBLE;
Index altered.
SQL> SELECT index_name, visibility
2 FROM user_indexes
3 WHERE index_name = 'IDX_INVISIBLE';
INDEX_NAME VISIBILIT
------------------------------ ---------
IDX_INVISIBLE VISIBLE
SQL> SELECT adate
2 FROM index_test
3 WHERE anumber = 567;
ADATE
---------
24-OCT-12
-----------------------------------------------------------------------------
| Id | Operation | Name |Rows |Bytes | Time |
-----------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 1 | 113 | 00:00:01 |
| 1| TABLE ACCESS BY INDEX ROWID| INDEX_TEST | 1 | 113 | 00:00:01 |
|* 2| INDEX RANGE SCAN | IDX_INVISIBLE | 1 | | 00:00:01 |
-----------------------------------------------------------------------------Now we can see that optimizer is using “IDX_INVISIBLE”, as it is now visible to optimizer, to access table “INDEX_TEST”. We can also make optimizer to see invisible index by setting parameter OPTIMIZER_USE_INVISIBLE_INDEXES.
SQL> ALTER INDEX idx_invisible INVISIBLE;
Index altered.
SQL> ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE;
Session altered.
SQL> SELECT adate
2 FROM index_test
3 WHERE anumber = 567;
ADATE
---------
24-OCT-12
-----------------------------------------------------------------------------
| Id | Operation | Name |Rows |Bytes | Time |
-----------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 1 | 113 | 00:00:01 |
| 1| TABLE ACCESS BY INDEX ROWID| INDEX_TEST | 1 | 113 | 00:00:01 |
|* 2| INDEX RANGE SCAN | IDX_INVISIBLE | 1 | | 00:00:01 |
-----------------------------------------------------------------------------We can see that although index “IDX_INVISIBLE” is invisible, the optimizer still can use it to access table “INDEX_TEST”.
Link to script that contains the examples in this post.