Oracle Invisible Index Tips

Invisible indexes

Oracle 11g introduces a new feature for indexes, invisible indexes, that is 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. 

One use of the invisible index feature is to test the removal of an index before dropping it.  Prior to 11g, this was typically achieved by making an index unusable during a set period of time.  During this observation period, the DBA would monitor the database performance to determine whether or not to drop the index.  If performance was negatively affected, the index would need to be rebuilt before it could be used again.  Beginning with Oracle 11g, the DBA has the option of making the index invisible as opposed to unusable during this observation period.  If performance degradation is observed, the index can be made visible again without having to rebuild the index.  This can minimize the period of performance degradation, while also preventing an expensive operation to rebuild or recreate an index.

The syntax to change the visibility of an index is simple.  To make an index invisible:

ALTER INDEX index_name INVISIBLE;

To make an index visible:

ALTER INDEX index_name VISIBLE;

It is possible to query the column ?VISIBILITY? from the data dictionary view dba_indexes to determine the current status of an index. 

Another potential use for invisible indexes is in situations where specific applications require an index temporarily.  An index can be created as invisible to allow specific SQL statements to use the index while leaving the rest of the database unaffected. Creating a visible index for this same purpose would cause the optimizer to consider the new index for all execution plans on that object. 

Consider the introduction of a reporting application into a large production database.  Shortly after the go-live, a query in the application is found to be running slow.  The query is consuming excessive amounts of resources due to a full table scan on a large, highly referenced table, order_lines.  After identifying this query, there is a realization that creating an index on column order_lines.attribute7 would immediately resolve this particular issue.  This issue needs to be resolved as soon as possible without impacting any other processes that use this object.  In this situation, creating an invisible index as a temporary solution until the application code can be reviewed would be an ideal solution. This method would alleviate the immediate problem without potentially affecting other users and processes that use the order_lines table. 

Create an invisible index on order_lines.attribute7:

create
   index order_lines_inv
on
   order_lines(ATTRIBUTE7) INVISIBLE;

This one query can be modified to explicitly use the invisible index with a hint:

select /*+ INDEX (order_lines ORDER_LINES_INV) */
   id
from
   order_lines
where
   attribute7 = 11001;

If the application code cannot be modified, it is possible to instruct the optimizer to include invisible indexes at the session level:

alter session set optimizer_use_invisible_indexes = true;

Keep in mind that rebuilding an invisible index will make it visible. 

Invisible indexes are an attractive feature for the process of dropping an index. They are also useful when a specific application needs the benefit of a temporary index without impacting the database on a wider scale.  Since the database must still maintain an invisible index for all DML operations, invisible indexes should not be used unless necessary.  Though they should be removed once their purpose has been served, invisible indexes offer substantial advantages for short-term solutions.  

Invisible Index Examples

The invisible index provides added functionality in that it can be used for testing performance scenarios and affects how the Oracle Cost Based Optimizer(CBO) behaves. For example, when the invisible index is used, the CBO ignores the index as if the index did not exist. If the Oracle 11g initialization parameter optimizer_use_invisible_indexesis set to TRUE, then the CBO will see the index. By default, the parameter is set to FALSE so that the CBO ignores the invisible index when using execution plans. The syntax to create a new invisible index with Oracle 11g is similar to other create index statements as shown in this example:

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------
optimizer_use_invisible_indexes      boolean     FALSE

SQL>

Now check to find the index has been created and is an invisible index with the following query.

INDEX_NAME                     INDEX_TYPE                  VISIBILITY
------------------------------ --------------------------- ----------
EMP_LOWER_IDX                  FUNCTION-BASED NORMAL       VISIBLE
EMP_INV_IDX                    NORMAL                      INVISIBLE
EMP_DEPARTMENT_IX              NORMAL                      VISIBLE
EMP_EMAIL_UK                   NORMAL                      VISIBLE
EMP_EMP_ID_PK                  NORMAL                      VISIBLE
EMP_JOB_IX                     NORMAL                      VISIBLE
EMP_MANAGER_IX                 NORMAL                      VISIBLE
EMP_NAME_IX                    NORMAL                      VISIBLE

8 rows selected. 

The key difference is use of the invisible statement in the syntax to create the invisible index. To see the affect of the invisible index on the optimizer behavior, use autotrace to show the execution plan.

Notice in the following example that the query performs a full table scan against the SCOTT.EMP table since the index is invisible.

     EMPNO ENAME             SAL
---------- ---------- ----------
      7876 ADAMS            1100
      7499 ALLEN            1600
      7698 BLAKE            2850
      7782 CLARK            2450
      7902 FORD             3000
      7900 JAMES             950
      7566 JONES            2975
      7839 KING             5000
      7654 MARTIN           1250
      7934 MILLER           1300
      7788 SCOTT            3000
      7369 SMITH             800
      7844 TURNER           1500
      7521 WARD             1250 

14 rows selected. 

Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------

Plan hash value: 150391907
--------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    14 |   196 |     4  (25)| 00:00:01 |
|   1 |  SORT ORDER BY     |      |    14 |   196 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   196 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

Now one can test the impact on the cost-based optimizer if the invisible index is made into a normal and visible index with the alter index?visible command as shown in the query:

When the query is rerun against the SCOTT.EMP table, the results should access the new index as shown in the following example query:

ENAME           EMPNO        SAL
---------- ---------- ----------
SMITH            7369        800
ALLEN            7499       1600
WARD             7521       1250
JONES            7566       2975
MARTIN           7654       1250
BLAKE            7698       2850
CLARK            7782       2450
SCOTT            7788       3000
KING             7839       5000
TURNER           7844       1500
ADAMS            7876       1100
JAMES            7900        950
FORD             7902       3000
MILLER           7934       1300 

14 rows selected.

Elapsed: 00:00:00.05

Execution Plan
----------------------------------------------------------

Plan hash value: 4170700152 

--------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
 
--------------------------------------------------------------------------------------
 
|   0 | SELECT STATEMENT            |        |    14 |   196 |     2   (0)| 00:00:01 |
 
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |    14 |   196 |     2   (0)| 00:00:01 |
 
|   2 |   INDEX FULL SCAN           | PK_EMP |    14 |       |     1   (0)| 00:00:01 |
 
--------------------------------------------------------------------------------------
 
Statistics
----------------------------------------------------------
        406  recursive calls
          0  db block gets
         81  consistent gets
          7  physical reads
          0  redo size
        803  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         11  sorts (memory)
          0  sorts (disk)
         14  rows processed

Toggle the index type back to invisible using the alter index?invisible command on the index as well.

One can also test by making the primary key index for pk_emp invisible:

ENAME           EMPNO        SAL
---------- ---------- ----------
SMITH            7369        800
ALLEN            7499       1600
WARD             7521       1250
JONES            7566       2975
MARTIN           7654       1250
BLAKE            7698       2850
CLARK            7782       2450


ENAME           EMPNO        SAL
---------- ---------- ----------
SCOTT            7788       3000
KING             7839       5000
TURNER           7844       1500
ADAMS            7876       1100
JAMES            7900        950
FORD             7902       3000
MILLER           7934       1300

14 rows selected.

Elapsed: 00:00:00.01 

Execution Plan
----------------------------------------------------------

Plan hash value: 150391907

-----------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    14 |   196 |     4  (25)| 00:00:01 |
|   1 |  SORT ORDER BY     |      |    14 |   196 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   196 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
 
Statistics
----------------------------------------------------------
        240  recursive calls
          0  db block gets
         55  consistent gets
          5  physical reads
          0  redo size
        803  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          7  sorts (memory)
          0  sorts (disk)
         14  rows processed
 

The beauty of using the invisible index feature for Oracle 11g is that it allows the developer and DBA staff to test performance for the index without the risk of dropping an index which can be time consuming and a potential risk to the production environment.