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

advertisements

_____________________________________________________________________________________________________________________

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

-------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------

| 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

----------------------------------------------------------------------------------

----------------------------------------------------------------------------------

| 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

----------------------------------------------------------------------------------

----------------------------------------------------------------------------------

| 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

-------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------

| 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)

_____________________________________________________________________________________________________________________

Website Stats

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

0 comments:

Post a Comment

Archive

Labels