Invisible indexes

This article may require cleanup to meet OraFAQ's quality standards. Please improve this article if you can.

An invisible index is an alternative to making an index unusable or even to drop it. An invisible index is maintained for any DML operation but is not used by the optimizer unless you explicitly specify the index with a hint. Applications often have to be modified without being able to bring the complete Application offline. Create invisible indexes temporarily for specialized non-standard operations, such as online application upgrades, without affecting the behavior of any existing application. Furthermore, invisible indexes can be used to test the removal of an index without dropping it right away, thus enabling a grace period for testing in production environments.

SQL> create table test
   (sno number(5),name varchar2(20));
Table created.

SQL> create index test_ind on test(sno);

Index created.

SQL> insert into test
    select employee_id,last_name from employees;

107 rows created.

SQL> commit;

Commit complete.

Above, we created a table called TEST, we inserted some data into TEST, and we created a INDEX on the SNO column.

SQL> select index_name,column_name from user_ind_columns
    where table_name='TEST';

INDEX_NAME                     COLUMN_NAME
------------------------------ ------------------------------
TEST_IND                       SNO

Here we checked our indexed column by user_ind_columns.

We are now going to check how the query will process by the oracle server.

SQL> explain plan for
    select * from test where sno in (106,135,200);

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------
Plan hash value: 519842909

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |     3 |    75 |     2   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR             |          |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| TEST     |     3 |    75 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | TEST_IND |     1 |       |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Here you can notice that the query is using INDEX RANGE SCAN on TEST_IND index.

SQL> desc user_indexes
Name                                                  Null?    Type
----------------------------------------------------- -------- ---------------
--------------------
INDEX_NAME                                            NOT NULL VARCHAR2(30)
...
VISIBILITY                                                     VARCHAR2(9)        <<<<NEW IN 11G
...

SQL> select index_name,visibility from user_indexes
    where table_name='TEST';

INDEX_NAME                     VISIBILIT
------------------------------ ---------
TEST_IND                       VISIBLE

SQL> alter index test_ind invisible;     <<<<<<< I want to invisible mine index.

Index altered.

Now we can check our index status from user_indexes which have new column VISIBILITY

SQL> select index_name,visibility from user_indexes
    where table_name='TEST';

INDEX_NAME                     VISIBILIT
------------------------------ ---------
TEST_IND                       INVISIBLE

you can see that index is invisible now its means oracle can't see it now.

now we again check the same explain plan for same query and compare the results.

SQL> explain plan for
  2  select * from test where sno in (106,135,200);

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     3 |    75 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |     3 |    75 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

now in above explain plan you can see oracle didn't use the index as it used in previous query.

SQL> select index_name,visibility from user_indexes
    where table_name='TEST';

INDEX_NAME                     VISIBILIT
------------------------------ ---------
TEST_IND                       INVISIBLE

SQL> alter index test_ind visible;

Index altered.

SQL> select index_name,visibility from user_indexes
    where table_name='TEST';

INDEX_NAME                     VISIBILIT
------------------------------ ---------
TEST_IND                       VISIBLE