DESCRIPTION
Oracle 11g introduces a new feature for indexes, invisible indexes. That can be useful in several different situations. An invisible index is an index that is maintained by the database but ignored by the optimizer unless explicitly specified. The invisible index is an alternative to dropping or making an index unusable. This feature is also functional when certain modules of an application require a specific index without affecting the rest of the application.
SYNTAX
CREATE INDEX index_name ON table_name(column_name) INVISIBLE;
ALTER INDEX index_name INVISIBLE;
ALTER INDEX index_name VISIBLE;
EXAMPLES
The following script creates and populates a table, then creates an invisible index on it.
CREATE TABLE test_tab (id NUMBER);
Table created.
BEGIN
FOR i IN 1 .. 10000 LOOP
INSERT INTO test_tab VALUES (i);
END LOOP;
COMMIT;
END;
/
PL/SQL procedure successfully completed.
CREATE INDEX test_idx ON test_tab(id) INVISIBLE;
Index created.
EXEC DBMS_STATS.gather_table_stats(USER, 'test_tab', cascade=> TRUE);
PL/SQL procedure successfully completed.
A query using the indexed column in the WHERE clause ignores the index and does a full table scan.
SET AUTOTRACE ON
SELECT * FROM test_tab WHERE id = 9999;
----------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 24 |
|* 1 | TABLE ACCESS FULL| TEST_TAB | 1 | 1 | 1 |00:00:00.01 | 24 |
----------------------------------------------------------------------------------------
Setting the OPTIMIZER_USE_INVISIBLE_INDEXES parameter makes the index available to the optimizer.
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE;
Session altered.
SELECT id FROM test_tab WHERE id = 9999;
---------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 3 |
|* 1 | INDEX RANGE SCAN| TEST_IDX | 1 | 1 | 1 |00:00:00.01 | 3 |
---------------------------------------------------------------------------------------
Making the index visible means it is still available to the optimizer when the OPTIMIZER_USE_INVISIBLE_INDEXES parameter is reset.
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=FALSE;
Session altered.
ALTER INDEX test_idx VISIBLE;
Index altered.
---------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rsows | A-Time | Buffers |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 3 |
|* 1 | INDEX RANGE SCAN| TEST_IDX | 1 | 1 | 1 |00:00:00.01 | 3 |
---------------------------------------------------------------------------------------
The current visibility status of an index is indicated by the VISIBILITY column of the [DBA|ALL|USER]_INDEXES views.
SELECT index_name, visibility FROM user_indexes WHERE index_name='TEST_IDX';
INDEX_NAME VISIBILITY
---------- --------------
TEST_IDX VISIBLE
