ViralPatel.net

The effective usage of an index is always a big question. There are instances in production environments, where an index may help the performance of just a single query, while it can degrade the performance of a number of queries. Always the Optimizer can’t guarantee the best suitable index required for the query in an environment which is cluttered by too many indexes on a table. One of the older solutions is to determine the effective usage of indexes in the database. Once the monitoring usage clause for the index is enabled, we can determine the usage of the index by querying v$object_usage data dictionary view. The index monitoring time again depends on the application, database usage and business hours. It varies from case to case. Some applications might require a few hours for monitoring the usage of indexes. Some might require even months to determine the perfect indexes that need to be retained and the ones that need to be detained. If the usage of the index is at all, or it results a bad performance to other queries, one can easily drop the index. However in future, if some adhoc queries require the index badly (where the index dropped would have increased the performance dramatically), we need to think reinstating the index again. The recreation of an index is a very expensive one and requires a lot of database resources and consumes a lot of time as well. One of the new features of oracle 11g, Invisible indexes is a perfect solution to the above problem. An index can be made invisible to the Optimizer, so that the optimizer can’t see it. If an adhoc query requires the usage of the index it can explicitly specify the index as a part of hint. The following example demonstrates invisible index better.

SQL> desc employee;
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------

 EMPID                                              NUMBER
 DEPTNO                                             NUMBER
 EMPNAME                                            VARCHAR2(30)
 MGR_NAME                                           VARCHAR2(30)
 ADDRESS                                            VARCHAR2(100)
Code language: SQL (Structured Query Language) (sql)

Let us create an index on EMPID column of the employee table.

SQL> create index idx_empid on employee(empid);

Index created.
Code language: SQL (Structured Query Language) (sql)

After creating the index let us fire this simple query:

SQL> select * from employee where empid = 1001;

Execution Plan
-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     1 |   112 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEE  |     1 |   112 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_EMPID |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Code language: SQL (Structured Query Language) (sql)

The above execution plan confirms the usage of the index which we created on the empid column. Now let us make this index invisible to the optimizer. An index can be made invisible using the simple ALTER statement.

SQL> alter index idx_empid invisible;

Index altered.
Code language: SQL (Structured Query Language) (sql)

The invisibility status of the index can be checked by using the following query:

SQL> select visibility from user_indexes where index_name = 'IDX_EMPID';

VISIBILITY
---------
INVISIBLE
Code language: SQL (Structured Query Language) (sql)

Now let us fire the same query on the employee table. The below explain plan shows the index became invisible to the optimizer and a full table scan is performed instead of index scan.

SQL> select * from employee where empid = 1001;

Execution Plan

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |     1 |   112 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMPLOYEE |     1 |   112 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------
Code language: SQL (Structured Query Language) (sql)

Let us now try to give an index hint in our above query.

SQL> select /*+ index(idx_empid) */ * from employee where empid = 1001;


Execution Plan

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     1 |   112 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEE  |     1 |   112 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_EMPID |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Code language: SQL (Structured Query Language) (sql)

After giving the hint, the optimizer used the index once again and performed an index scan. From the above example it is clear that, when an index can be made invisible only to the Optimizer and not to the user. User can still use it by giving a hint to the optimizer where ever it is required. Alternatively, there is a session level parameter, optimizer_use_invisible_indexes. This parameter can be set to TRUE, so that all the invisible indexes can be made visible to the optimizer.

SQL> alter session set optimizer_use_invisible_indexes = true;
Code language: SQL (Structured Query Language) (sql)

Advantages of Invisble Indexes

1. Invisible indexes can help to a great extent, in testing scenarios where the optimizer behavior needs to be tested without the index. This does not need dropping and recreating the index.
2. Indexes can be created for certain adhoc queries (queries that are fired very infrequently) and can be made invisible after the usage.

However, invisible indexes behave the same like a normal index towards the DML operations. They need to be updated with each DML operation.