Database Design

Invisible Indexes

User Rating:

/ 0

Details: Parent Category: Articles; Created on Saturday, 28 August 2010 16:50; Last Updated on Thursday, 24 October 2013 21:01; Published on Saturday, 28 August 2010 16:50; Written by Guy Lambregts; Hits: 3556

Invisible Indexes

Invisible Indexes are not the same as virtual indexes. Virtual indexes are no segment indexes they do not exist physically. Invisible indexes exist physically they are maintained by the database during DML operations but

1. they are only used when explicitly referenced

2. they are used when we instruct the optimizer at the session level to take them into account

3. they are used when we instruct the optimizer at instance level to take them into account

4. a part from the above they are not used / considered by the CBO.

Imagine you have a severe performance issue. As a DBA you are asked to solve it as fast as possible. Does not sound that strange if you ask me. You found the guilty sql causing the severe performance degradation and you have found that the creation of a additional B-Tree index would significantly improve the performance. However you are dealing with a validated environment, you are actually not allowed to change something what could possible change the execution plan of other queries. That does still sound very familiar to me. Invisible indexes are a big deal with respect to the above problem. They are invisible when we want, they become visible --- read usable --- when we want and this without the need of a drop or an expensive rebuild of that index.

Let us make this "visible" with the below gym

SQL> select table_name,index_name,visibility from user_indexes where table_name in ('T_EXCHANGERATE_ER','T_PRECIOUSMETALS_PM');

TABLE_NAME INDEX_NAME VISIBILITY

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

T_EXCHANGERATE_ER SYS_C0013127 VISIBLE

T_EXCHANGERATE_ER IDX_FK_ER_PM VISIBLE

T_PRECIOUSMETALS_PM SYS_IOT_TOP_75299 VISIBLE

SQL> desc T_PRECIOUSMETALS_PM;

Name Null? Type

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

PM_PK NOT NULL VARCHAR2(2)

PM_NAME VARCHAR2(20)

PM_IS_PGM NOT NULL VARCHAR2(3)

SQL> desc T_EXCHANGERATE_ER;

Name Null? Type

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

ER_PK NOT NULL NUMBER

ER_FK_PM NOT NULL VARCHAR2(2)

ER_RATE NOT NULL NUMBER

ER_TIME NOT NULL TIMESTAMP(6)

SQL> select * from T_PRECIOUSMETALS_PM;

PM PM_NAME PM_IS_PGM

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

Ag Silver NO

Au Gold NO

IR Iridium YES

OS Osmium YES

PD Palladium YES

PT Platinium YES

RU Ruthenium YES

7 rows selected.

SQL> select * from t_exchangerate_er er, t_preciousmetals_pm pm where er.er_fk_pm = pm.pm_pk and pm_pk='PD';

Execution Plan

Plan hash value: 2038083173

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

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

| 0 | SELECT STATEMENT | | 346 | 18684 | 4 (0)| 00:00:01 |

| 1 | NESTED LOOPS | | 346 | 18684 | 4 (0)| 00:00:01 |

|* 2 | INDEX UNIQUE SCAN | SYS_IOT_TOP_75299 | 1 | 14 | 0 (0)| 00:00:01 |

| 3 | TABLE ACCESS BY INDEX ROWID | T_EXCHANGERATE_ER | 346 | 13840 | 4 (0)| 00:00:01 |

|* 4 | INDEX RANGE SCAN | IDX_FK_ER_PM | 346 | | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

2 - access("PM_PK"='PD')

4 - access("ER"."ER_FK_PM"='PD')

Here I show how to make an index invisible

SQL> alter index idx_fk_er_pm invisible;

Index altered.

And here I show that the invisible index is not used anymore by the optimizer

SQL> set autotrace off

SQL> select table_name,index_name from user_indexes where table_name in('T_EXCHANGERATE_ER','T_PRECIOUSMETALS_PM');

TABLE_NAME INDEX_NAME VISIBILITY

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

T_EXCHANGERATE_ER SYS_C0013127 VISIBLE

T_EXCHANGERATE_ER IDX_FK_ER_PM

T_PRECIOUSMETALS_PM SYS_IOT_TOP_75299 VISIBLE

SQL> set autotrace traceonly explain

SQL> select * from t_exchangerate_er er, t_preciousmetals_pm pm where er.er_fk_pm = pm.pm_pk and pm_pk='PD';

Execution Plan

Plan hash value: 1497474858

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

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

| 0 | SELECT STATEMENT | | 346 | 18684 | 226 (1)| 00:00:03 |

| 1 | NESTED LOOPS | | 346 | 18684 | 226 (1)| 00:00:03 |

|* 2 | INDEX UNIQUE SCAN| SYS_IOT_TOP_75299 | 1 | 14 | 0 (0)| 00:00:01 |

|* 3 | TABLE ACCESS FULL| T_EXCHANGERATE_ER | 346 | 13840 | 226 (1)| 00:00:03 |

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

Predicate Information (identified by operation id):

2 - access("PM_PK"='PD')

3 - filter("ER"."ER_FK_PM"='PD')

And here I show that the invisible index is used when we instruct the optimizer to consider them

SQL> alter session set optimizer_use_invisible_indexes=true;

Session altered.

SQL> select * from t_exchangerate_er er, t_preciousmetals_pm pm where er.er_fk_pm = pm.pm_pk and pm_pk='PD';

Execution Plan

Plan hash value: 2038083173

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

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

| 0 | SELECT STATEMENT | | 346 | 18684 | 4 (0)| 00:00:01 |

| 1 | NESTED LOOPS | | 346 | 18684 | 4 (0)| 00:00:01 |

|* 2 | INDEX UNIQUE SCAN | SYS_IOT_TOP_75299 | 1 | 14 | 0 (0)| 00:00:01 |

| 3 | TABLE ACCESS BY INDEX ROWID| T_EXCHANGERATE_ER | 346 | 13840 | 4 (0)| 00:00:01 |

|* 4 | INDEX RANGE SCAN | IDX_FK_ER_PM | 346 | | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

2 - access("PM_PK"='PD')

4 - access("ER"."ER_FK_PM"='PD')

Here I make the index visible again

SQL> alter index idx_fk_er_pm visible;

Index altered.

Let us take a look to the query below. We notice the index idx_fk_er_pm is used.

SQL> select max(er_rate) from t_exchangerate_er where er_fk_pm='PD';

Execution Plan

Plan hash value: 713366416

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

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

| 0 | SELECT STATEMENT | | 1 | 25 | 4 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 25 | | |

| 2 | TABLE ACCESS BY INDEX ROWID| T_EXCHANGERATE_ER | 346 | 8650 | 4 (0)| 00:00:01 |

|* 3 | INDEX RANGE SCAN | IDX_FK_ER_PM | 346 | | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

3 - access("ER_FK_PM"='PD')

However we wonder whether a composite index on the couple (er_fk_pm, er_rate) would be useful ? We create it as an invisible index.

When we repeat the statement we notice that the invisible index is not used.

SQL> create index IDX_ER_PM_ER_RATE on t_exchangerate_er (er_fk_pm,er_rate) invisible;

Index created.

SQL> exec dbms_stats.gather_table_stats('PM','T_EXCHANGERATE_ER',cascade=>true);

PL/SQL procedure successfully completed.

SQL> set autotrace traceonly explain

SQL> select max(er_rate) from t_exchangerate_er where er_fk_pm='PD';

Execution Plan

Plan hash value: 713366416

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

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

| 0 | SELECT STATEMENT | | 1 | 25 | 3 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 25 | | |

| 2 | TABLE ACCESS BY INDEX ROWID| T_EXCHANGERATE_ER | 251 | 6275| 3 (0)| 00:00:01 |

|* 3 | INDEX RANGE SCAN | IDX_FK_ER_PM | 251 | | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

3 - access("ER_FK_PM"='PD')

The invisible index we just created isn' t used. Let' s inform to the optimizer to consider the invisble index, and we notoce while we execute the same statement, the index will be used

SQL> alter session set optimizer_use_invisible_indexes=true;

Session altered.

SQL> select max(er_rate) from t_exchangerate_er where er_fk_pm='PD';

Execution Plan

Plan hash value: 2664687005

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

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

| 0 | SELECT STATEMENT | | 1 | 25 | 3 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 25 | | |

| 2 | FIRST ROW | | 251 | 6275 | 3 (0)| 00:00:01 |

|* 3 | INDEX RANGE SCAN (MIN/MAX)| IDX_ER_PM_ER_RATE | 251 | 6275 | 3 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

3 - access("ER_FK_PM"='PD')

Conclusion :

Invisible indexes offer us a flexible way to hide indexes to the optimizer whenever we want but also we render them visible in seconds and have them considered by to the optimizer whenever we want. Is this an interesting new feature ? yes it is.