Systems Engineering and RDBMS

Archive for December 4th, 2007

Invisible Index in Oracle 11g

Posted by decipherinfosys on December 4, 2007

In release 11g, Oracle introduced a new feature of making an index invisible. During the index creation, one can specify whether index can be visible or invisible. We can also alter index to be visible or invisible. Invisible index is ignored by optimizer when computing the plan for a SQL.  Let’s look into this using an example:

CREATE TABLE TEST
(
TEST_ID NUMBER(9) NOT NULL,
TEST_OBJECT VARCHAR(30),
CONSTRAINT PK_TEST PRIMARY KEY(TEST_ID)
)
/

INSERT INTO TEST(TEST_ID,TEST_OBJECT)
SELECT OBJECT_ID,OBJECT_NAME
FROM ALL_OBJECTS
/

CREATE INDEX TEST_IND_1 ON TEST(TEST_OBJECT) VISIBLE
/

OR

CREATE INDEX TEST_IND_1 ON TEST(TEST_OBJECT)
/

Here in first CREATE INDEX statement, we have used VISIBLE clause. By default, Oracle creates visible index. We can verify this by accessing USER_INDEXES view.

SELECT index_name, visibility
FROM user_indexes WHERE index_name = ‘TEST_IND_1′;
/

INDEX_NAME                     VISIBILIT
—————————— ———
TEST_IND_1                     VISIBLE

Now let us enable the trace and execute the following query.

SET AUTOTRACE TRACEONLY;

SELECT *
FROM TEST
WHERE TEST_OBJECT = ‘USER_TABLES';

Here is the execution plan for the statement which indicates the index usage.

——————————————————————————————
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)|Time     |
——————————————————————————————
|   0 | SELECT STATEMENT            |            |     2 |    60 |     2   (0)|00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST       |     2 |    60 |     2   (0)|00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TEST_IND_1 |     2 |       |     1   (0)|00:00:01 |
——————————————————————————————

Now let us make this index invisible by altering it and execute the same statement again.

ALTER INDEX TEST_IND_1 INVISIBLE;

SELECT *
FROM TEST
WHERE TEST_OBJECT = ‘USER_TABLES';

Execution plan below confirms that index is totally ignored as Oracle performs full table scan.

————————————————————————–
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
————————————————————————–
|   0 | SELECT STATEMENT  |      |     4 |   120 |   103   (1)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| TEST |     4 |   120 |   103   (1)| 00:00:02 |
————————————————————————–

Making index is a very useful alternative to make index unusable or drop it completely. It is better alternative to unusable index because, even if the index is invisible, it is maintained during DML operations where as unusable index is not maintained and we need to rebuild it to make it available or online.  It can be useful in different scenarios like
•    How optimizer will behave in case index in question is dropped. We can make index invisible instead of dropping it and check the effect.
•    Indexes can be created for infrequent scenarios to speed up certain operations and we can make them invisible so that it does not affect the overall performance of the application.

There is a parameter to make invisible indexes available to optimizer. If user explicitly sets the initialization parameter OPTIMIZER_USE_INVISIBLE_INDEXES is set to TRUE then optimizer can use invisible index.

Posted in Oracle | Leave a Comment »

 
Follow

Get every new post delivered to your Inbox.

Join 77 other followers