19 | 08 | 2017
Latest Articles
Popular Articles

Database Design

Invisible Indexes

User Rating:  / 0
PoorBest 

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

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

| Id | Operation                   | Name              | Rows | Bytes | Cost(%CPU)|     Time |

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

|  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

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

| Id | Operation        |              Name | Rows | Bytes | Cost (%CPU)|     Time |

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

|  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

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

| Id | Operation                  |              Name | Rows | Bytes | Cost(%CPU)|     Time |

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

|  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

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

| Id | Operation                  |              Name | Rows | Bytes | Cost(%CPU)|     Time |

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

|  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

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

| Id | Operation                  |              Name | Rows | Bytes | Cost(%CPU)|     Time |

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

|  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

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

| Id | Operation                 |              Name | Rows | Bytes | Cost(%CPU)|     Time |

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

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