Oracle 11g Invisible Index

Oracle 11g release has introduced a new property of an Index i.e. Visibility. Before the world saw 11g release, an index was always created and existed in visible mode. But now, as per the fresh feature in Oracle 11g, an index may exist in Invisible Mode too. The optimizer may (may not) use the index during query execution.

In this tutorial, I shall familiarize you with the Visibility mode of an Index, its impact and analysis in sync with my findings.

Invisible Index: The Cause and Introduction

SQL tuning is a hit and trial method with majority of cases. Indexing is one of the primary actions in SQL tuning. If a complex SQL query has to be fine tuned, multiple indexing options follow different access path and can yield varied performance. Prior to Oracle 11g, multiple indexing options have to be created, tested and dropped before moving to the nest option. This ‘create and drop index’ approach appeared a crude solution for complex query tuning.

With 11g release, Invisible Index provides a discrete solution to handle scenarios of above sort. Multiple indexing options can be created in INVISIBLE mode. At a time, one (or more) out of many can be made visible to the optimizer. Now, let the optimizer use the available visible indexes and arrive at the performance matrix. The approach can be practiced until the best indexing option is identified.

A new initialization parameter OPTIMIZER_USE_INVISIBLE_INDEX has been introduced to govern the usage of invisible index by the optimizer.

Invisible Index: Syntax and Illustration

By default, an index is always created in VISIBLE mode. The syntax for the creation of an invisible index shows the inclusion of a new INVISIBLE keyword.

Syntax [1]

CREATE INDEX [INDEX NAME] ON [TABLE NAME (COLUMN NAME)] INVISIBLE;

Code [1]

The statement below creates an index T_INV_IDX on the EMPLOYEE_ID column of EMPLOYEES table.

SQL> CREATE INDEX T_INV_IDX ON EMPLOYEES (EMPLOYEE_ID) INVISIBLE;
 
INDEX created.

An existing index can be altered to toggle over the visibility modes.

Syntax [2]

ALTER INDEX [INDEX NAME] [VISIBLE | INVISIBLE]

A VISIBLE index T_INV_IDX is altered to switch to INVISIBLE mode.

SQL> ALTER INDEX T_INV_IDX INVISIBLE; 
 
INDEX altered.

OPTIMIZER_USE_INVISIBLE_INDEX is a boolean parameter which can be set as either TRUE or FALSE. By default, the new initialization parameter OPTIMIZER_USE_INVISIBLE_INDEX is set to FALSE.

If the parameter is set as FALSE, optimizer would ignore the invisible index and use only the visible index while designing the query access path. If it is TRUE, optimizer would considerate all the indexes on a table, irrespective of their visibility modes. Vocally, this setting (OPTIMIZER_USE_INVISIBLE_INDEX as TRUE) nullifies the purpose of Invisible Indexes.

Its value can be changed using ALTER [SYSTEM | SESSION] command.

Invisible Index: Demonstration and Impact analysis

I shall demonstrate the impact and usage of Invisible index with the help of below illustration.
For dedicated testing, I shall create a fresh table.

Code [3a]

SQL> CREATE TABLE T_INV_IND
    (COL1 NUMBER,
     COL2 NUMBER,
     COMM VARCHAR2(100)
     );
 
TABLE created.

Now, we insert huge volume of test data to increase the scalability and precision of index usage.

Code [3b]

SQL> INSERT INTO T_INV_IND
SELECT ROWNUM * 2, ROWNUM * 3, ‘Comment – ‘||ROWNUM
FROM DUAL
CONNECT BY ROWNUM < 1000000;
 
1000000 ROWS inserted.

Adding the primary key constraint on COL1 of the table

Code [3c]

SQL> ALTER TABLE T_INV_IND ADD PRIMARY KEY(COL1);
 
TABLE altered.

Adding a B-tree Invisible Index on COL2 column of the table

Code [3d]

SQL> CREATE INDEX T_INV_IDX1 ON T_INV_IND(COL2) INVISIBLE;
 
INDEX created.

A new column ‘VISIBILITY’ has been added to USER_INDEXES dictionary view to maintain the visibility mode of the index. For the above index T_INV_IDX1, the status is updated as INVISIBLE.

Example Code [3e]

SQL> SELECT INDEX_NAME, TABLE_NAME, VISIBILITY FROM USER_INDEXES WHERE INDEX_NAME = ‘T_INV_IDX1';
 
INDEX_NAME    TABLE_NAME                     VISIBILIT
------------- ------------------------------ ---------
T_INV_IDX1    T_INV_IND                      INVISIBLE

Case 1: OPTIMIZER_USE_INVISIBLE_INDEX = FALSE and INVISIBLE Index

In this case, the optimizer would ignore the index during query access path optimization. This case efficiently demonstrates the impact of visible mode on a query.

The below query has been executed and its explain plan can be generated

Code [4a]

SQL> SELECT * FROM T_INV_IND WHERE COL2 < 50;

Inference: The explain matrix shows that the optimizer has undergone full scan of the table T_INV_IND to retrieve the query result set. The optimizer has ignored the invisible index on COL2 of the table.

Using hints to enforce invisible index

Alternatively, in the situations as in Case 1, an invisible index can be enforced in a query using /*+INDEX*/ hint. The optimizer forcibly uses the index in the query access path.

The index T_INV_IDX1 was altered to INVISIBLE mode.

Code [4b]

ALTER INDEX T_INV_IDX1 INVISIBLE 
 
INDEX altered.

Now, explain plan for the below query can be generated to demonstrate the usage of hints with invisible indexes.

Code [4c]

SELECT /*+ INDEX(T_INV_IDX1) */ *
FROM T_INV_IND
WHERE COL2 < 50;

Inference: Oracle hint overrides the invisible effect of an index. Note the explain plan matrix is same as that in Case 1.

Case 2: OPTIMIZER_USE_INVISIBLE_INDEX = FALSE and VISIBLE Index

Now, we make the index visible to infer the impact of visible mode.

Code [5a]

SQL> ALTER INDEX t_inv_idx1 visible;
 
INDEX altered.

The status changes to VISIBLE mode in the USER_INDEXES view.

Code [5b]

SQL> SELECT INDEX_NAME, TABLE_NAME, VISIBILITY FROM USER_INDEXES WHERE INDEX_NAME = ‘T_INV_IDX1';
 
INDEX_NAME    TABLE_NAME                     VISIBILIT
------------- ------------------------------ ---------
T_INV_IDX1    T_INV_IND                      VISIBLE

Now, we shall query the T_INV_IND table with COL2 column to generate the explain plan of the query. The PLAN_TABLE_OUTPUT shows the performance matrix of the query.

Inference: As per the explain plan, the optimizer uses the index T_INV_IDX1 to perform Index Range Scan. This implies that an Invisible Index behaves like a normal index in visible state.

Case 3: OPTIMIZER_USE_INVISIBLE_INDEX = TRUE and INVISIBLE Index

The initialization parameter was changed to TRUE. This setting can be done by DBA or privileged users. The change directs optimizer to use indexes in invisible mode, while deciding query access path.

After Case 2, the index T_INV_IDX1 still remains in INVISIBLE mode.

Code [6a]

SQL> SELECT INDEX_NAME, TABLE_NAME, VISIBILITY FROM USER_INDEXES WHERE INDEX_NAME = ‘T_INV_IDX1';
 
INDEX_NAME    TABLE_NAME                     VISIBILIT
------------- ------------------------------ ---------
T_INV_IDX1    T_INV_IND                      INVISIBLE

Now, we shall execute the same query as in Case 1 to study the impact of parametric change on the optimizer.

Inference: The explain plan matrix shows the index range scan and usage of T_INV_IDX1 index. If the parameter OPTIMIZER_USE_INVISIBLE_INDEX is TRUE, visibility mode of an index is nullified.

[catlist id=185].