//
you're reading...
Administration

Invisible Index

Starting with Oracle Database 11g you can create or mark an index as invisible index. It can be used as an alternative to marking an index as unusable or dropping it. The other benefits of making an index as invisible is to test the effect of dropping it permanently from database or using it in some special scenario before making it official.

An invisible index is not considered by the optimizer when it’s creating the most cost-efficient and optimal execution plan. By default all indexes are visible to optimizer. The new clause in CREATE INDEX statement lets you make an index either visible or invisible.

CRAETE INDEX <index_name> . . . . . VISIBLE|INVISIBLE;

If you don’t use any of the clauses, oracle will create a normal index. Apart from creating invisible index, you can make an existing index as invisible. This feature helps you to make selective use of an index, sometimes invisible to optimizer and other time available to it.

The first example creates an invisible index and second making a normal index as invisible.

CREATE INDEX inv_ind_stu ON STUDENT(id) INVISIBLE
TABLESPACE tab_indx;

ALTER INDEX <index_name> INVISIBLE;

Database treats invisible index same as normal index, the only difference is its unknown to the cost optimizer. It maintained by database when there are Regular database operations such as inserts, updates, and deletes will continue to update the rows of tables.

tarzan@O11R2> SELECT cust_id, cust_first_name||' '||cust_last_name "Name"
             FROM tarzan.customers
             WHERE cust_gender='M';
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3726190457

------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           | 34839 |  1701K|   115   (0)| 00:00:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID | CUSTOMERS | 34839 |  1701K|   115   (0)| 00:00:02 |
|   2 |   BITMAP CONVERSION TO ROWIDS|           |       |       |            |          |
|*  3 |    BITMAP INDEX SINGLE VALUE | CUST_GEN  |       |       |            |          |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("CUST_GENDER"='M')

Now make the index invisible.

tarzan@O11R2>ALTER INDEX cust_gen INVISIBLE;
Index altered.

tarzan@O11R2> SELECT cust_id, cust_first_name||' '||cust_last_name "Name"
             FROM tarzan.customers
             WHERE cust_gender='M';


Execution Plan
----------------------------------------------------------
Plan hash value: 2008213504

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           | 34839 |  1701K|   406   (1)| 00:00:05 |
|*  1 |  TABLE ACCESS FULL| CUSTOMERS | 34839 |  1701K|   406   (1)| 00:00:05 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("CUST_GENDER"='M')

Although invisible index is not seen by optimizer, you can override this functionality by using the following methods describe:

If you want all invisible indexes of your database to be seen by optimizer, change the value of optimizer_use_invisible_indexes initialization parameter to TRUE in the system or session level. The default value is FALSE. This will cause the optimizer to treat all invisible indexes as normal index.

ALTER SESSION SET optimizer_use_invisible_indexes=TRUE;
ALTER SYSTEM SET optimizer_use_invisible_indexes=TRUE;
tarzan@O11R2> ALTER SESSION SET optimizer_use_invisible_indexes=TRUE;
Session altered.

tarzan@O11R2> SELECT cust_id, cust_first_name||' '||cust_last_name "Name"
    FROM tarzan.customers
    WHERE cust_gender='M';
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3726190457

------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           | 34839 |  1701K|   112   (0)| 00:00:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID | CUSTOMERS | 34839 |  1701K|   112   (0)| 00:00:02 |
|   2 |   BITMAP CONVERSION TO ROWIDS|           |       |       |            |          |
|*  3 |    BITMAP INDEX SINGLE VALUE | CUST_GEN  |       |       |            |          |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("CUST_GENDER"='M')

The above method makes all invisible indexes treated as normal indexes. If you want to available specific index to optimizer, you can do this by using index hint in the query. When optimizer sees the index hint embedded in the statement, it can use that index to evaluate optimal execution plan for the query.

tarzan@O11R2>ALTER INDEX cust_gen INVISIBLE;
Index altered.

tarzan@O11R2>SELECT /*+ index (CUST_GEN) */ count(cust_id)
             FROM tarzan.customers
             WHERE cust_gender='M';

COUNT(CUST_ID)
--------------
         37175


Execution Plan
----------------------------------------------------------
Plan hash value: 1910143616

------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |     1 |     3 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE               |          |     1 |     3 |            |          |
|   2 |   BITMAP CONVERSION COUNT     |          | 34839 |   102K|     3   (0)| 00:00:01 |
|*  3 |    BITMAP INDEX FAST FULL SCAN| CUST_GEN |       |       |            |          |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("CUST_GENDER"='M')

tarzan@O11R2>SHOW PARAMETER optimizer_use_invisible_indexes
NAME	                               TYPE             VALUE
-------------------------------        -----------      --------------
optimizer_use_invisible_indexes	       boolean          FALSE

tarzan@O11R2>SELECT  count(cust_id)
             FROM tarzan.customers
             WHERE cust_gender='M';

COUNT(CUST_ID)
--------------
         37175


Execution Plan
----------------------------------------------------------
Plan hash value: 296924608

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 |     3 |   406   (1)| 00:00:05 |
|   1 |  SORT AGGREGATE    |           |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| CUSTOMERS | 34839 |   102K|   406   (1)| 00:00:05 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("CUST_GENDER"='M')

You can query the visibility column of the USER_INDEXES view to check the current setting for the index.

tarzan@O11R2>SELECT index_name, visibility
             FROM user_indexes;

INDEX_NAME                     VISIBILITY
--------------------           ------------
CUST_PK_ID                     VISIBLE
CUST_GEN                       INVISIBLE

NOTE:

  • When you rebuild an invisible index, the index will become visible. You have to explicitly use INVISIBLE clause make it invisible.

Advertisements

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: