Oracle 11g New Feature: Invisible index advantages for performance tuning

Oracle 11g introduced a new feature called invisible indexes. We can make an index to invisible mode when it is not needed. Once we make an index to invisible the index will not come to optimizer reach. The syntax for making an index to invisible is as below.
ALTER INDEX index_name INVISIBLE;
ALTER INDEX index_name VISIBLE;


Invisible index is maintained by Oracle database but, ignored by the optimizer unless you specify explicitly.
How to check the visibility parameter of an index?
It can be identified from VISIBILITY column of the dba_indexes table.
SQL>select index_name, visibility from dba_indexes where index_name='EXAMPLE_INVI_IDX';

INDEX_NAME                     VISIBILIT
------------------------------ ---------
EXAMPLE_INVI_IDX               VISIBLE
SQL> alter index EXAMPLE_INVI_IDX invisible;

Index altered.

SQL> select index_name, visibility from dba_indexes where index_name='EXAMPLE_INVI_IDX';

INDEX_NAME                     VISIBILIT
------------------------------ ---------
EXAMPLE_INVI_IDX               INVISIBLE

Note:- Rebuilding an invisible index will not makes the index visible
SQL> select index_name, visibility from dba_indexes where index_name='EXAMPLE_INVI_IDX';

INDEX_NAME                     VISIBILIT
------------------------------ ---------
EXAMPLE_INVI_IDX               INVISIBLE

SQL> alter index EXAMPLE_INVI_IDX rebuild online;

Index altered.

SQL> select index_name, visibility from dba_indexes where index_name='EXAMPLE_INVI_IDX';

INDEX_NAME                     VISIBILIT
------------------------------ ---------
EXAMPLE_INVI_IDX               INVISIBLE

These are few advantages with this feature.
  • It can be used for disabling or making invisible a problematic (large in size) index causing performance issues. You can make it visible once after the issue has been addressed properly.
  • If you wanted to test some newly introduced index without affecting the other operations. The new index can put in invisible mode which will not affect the other normal operations with the table and complete the testing. Once the testing is completed and it is giving x times better performance the index can be make it to visible mode.
Note: once you make one index invisible mode it will not make any difference in the execution plans of normal table operations.
  • Invisible indexes are very useful during some certain performance issues. Suppose if you don’t wanted to use a large index during performance issue, prior to 11g we need to drop the index or make it unusable and test the scenario. But this 11g feature simplifies this method without dropping the index. Index rebuild is needed to make an unusable index to usable and it consumes time.
  • Invisible indexes can be used in only for specific applications without changing the execution plans for the other modules as it can be enabled or make it visible for session specific.
  • Another potential advantage of invisible index is the situations where we need an index as temporary purpose. An invisible index can be created temporarily for specific SQL statements without affecting the rest of the database.
  • Invisible indexes are alternative for dropping an index just for testing some performance issue scenarios.
In this example I have created one table table from all_objects dictionary view and added one column called ID. The value ID column is updated with rownum.
SQL> desc example_invi
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(128)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 OBJECT_ID                                          NUMBER
 DATA_OBJECT_ID                                     NUMBER
 OBJECT_TYPE                                        VARCHAR2(19)
 CREATED                                            DATE
 LAST_DDL_TIME                                      DATE
 TIMESTAMP                                          VARCHAR2(19)
 STATUS                                             VARCHAR2(7)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)
 NAMESPACE                                          NUMBER
 EDITION_NAME                                       VARCHAR2(30)
 ID                                                 NUMBER(6)

I am going to create an index on ID column of above table.

SQL> create index example_invi_idx on example_invi(id);

Index created.

Below I am going to test the index with visible mode and it is taking the index in the execution plan.

SQL> select object_name, owner, id from EXAMPLE_INVI where id=1045;

Execution Plan
----------------------------------------------------------
Plan hash value: 1697512530

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |     1 |    36 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EXAMPLE_INVI     |     1 |    36 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EXAMPLE_INVI_IDX |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   2 - access("ID"=1045)

Now I am going to make the index to invisible and see the execution plan with full table scan.

SQL> alter index EXAMPLE_INVI_IDX invisible;

Index altered.

SQL> l
  1* select object_name, owner, id from EXAMPLE_INVI where id=1045
SQL> /

Execution Plan
----------------------------------------------------------
Plan hash value: 1796624283

----------------------------------------------------------------------------------
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |              |    16 |  1536 |   337   (7)| 00:00:05 |
|*  1 |  TABLE ACCESS FULL| EXAMPLE_INVI |    16 |  1536 |   337   (7)| 00:00:05 |
----------------------------------------------------------------------------------

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

   1 - filter("ID"=1045)

Note
-----
   - dynamic sampling used for this statement

Even if you use index hint also it won’t make any difference to the execution plan.

SQL> select /*+ index (EXAMPLE_INVI EXAMPLE_INVI_idx)*/ object_name, owner, id from EXAMPLE_INVI where id=1045;

Execution Plan
----------------------------------------------------------
Plan hash value: 1796624283

----------------------------------------------------------------------------------
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |              |     1 |    36 |   335   (6)| 00:00:05 |
|*  1 |  TABLE ACCESS FULL| EXAMPLE_INVI |     1 |    36 |   335   (6)| 00:00:05 |
----------------------------------------------------------------------------------

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

   1 - filter("ID"=1045)

How to make an invisible index into action?

Enabling visibility at session/system level (optimizer_use_invisible_indexes)
SQL> alter session set optimizer_use_invisible_indexes = true;

Session altered.

SQL>  select object_name, owner, id from EXAMPLE_INVI where id=1045;

Execution Plan
----------------------------------------------------------
Plan hash value: 1697512530

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |     1 |    36 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EXAMPLE_INVI     |     1 |    36 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EXAMPLE_INVI_IDX |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   2 - access("ID"=1045)
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google