invisible index

In oracle, the default index information should be recorded in oracle CBO, and CBO used the index to decrease the selective cost  compare with the order execution plan. But is it truth and best that query sentence using indexes better by CBO ?

Usually, we will encounter  one sql which would be run quickly by creating one proper index, but this index will decrease the performance for order sqls. That is not useful for increasing one sql but decreasing more than two sqls. We also test the index which is or not  used by sql via invalid or dropping the index, but we should recreate the index when the sql needs to use the index. If making the index invisible the oracle CBO will not use the index and it do need not recreate the index when the sql needs the index, because the DML operation do maintain the index.

How to create invisible index 

Syntax:

CREATE INDEX index_name ON table_name(column_name) INVISIBLE

ALTER INDEX index_name INVISIBLE

ALTER INDEX index_name VISIBLE

eg:

SQL> create index idx_deptno_emp1 on emp1(deptno);

Index created.

SQL> select * from emp1 where deptno = 20;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      8801 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20


Execution Plan
----------------------------------------------------------
Plan hash value: 3783658255

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

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

   2 - access("DEPTNO"=20)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
       1241  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed

Make the index idx_deptno_emp1 invisible

SQL> alter index idx_deptno_emp1 invisible;

Index altered.

SQL>
SQL>
SQL> select * From emp1 where deptno=20;


Execution Plan
----------------------------------------------------------
Plan hash value: 2226897347

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

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

   1 - filter("DEPTNO"=20)


Statistics
----------------------------------------------------------
         31  recursive calls
          0  db block gets
         34  consistent gets
          0  physical reads
          0  redo size
       1221  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          5  rows processed

SQL> alter index idx_deptno_emp1 visible;

Index altered.

SQL>
SQL>
SQL> select * from emp1 where deptno = 20;


Execution Plan
----------------------------------------------------------
Plan hash value: 3783658255

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

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

   2 - access("DEPTNO"=20)


Statistics
----------------------------------------------------------
         43  recursive calls
          0  db block gets
         31  consistent gets
          0  physical reads
          0  redo size
       1241  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          5  rows processed

SQL>

If you create one index, but you don’t know that do it increase the performance of the sql. In this condition you do create an invisible index , and make it can be used by the oracle CBO in the test session via exec “ALTER SESSION SET optimizer_use_invisible_indexes=TRUE” and if you make sure that will be increase the sql performance , you should make the index be visible.

eg:

create index idx_detno_emp1 on emp1(deptno) invisible;

SQL> select * from emp1 where deptno = 20;


Execution Plan
----------------------------------------------------------
Plan hash value: 2226897347

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

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

   1 - filter("DEPTNO"=20)


Statistics
----------------------------------------------------------
         43  recursive calls
          0  db block gets
         34  consistent gets
          0  physical reads
          0  redo size
       1221  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          5  rows processed

SQL>

SQL> alter session set optimizer_use_invisible_indexes=true;

Session altered.

SQL> select * from emp1 where deptno=20;


Execution Plan
----------------------------------------------------------
Plan hash value: 3783658255

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

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

   2 - access("DEPTNO"=20)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
       1241  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed


And you can make all invisible index be used via executing “alter system set optimizer_use_invisible_indexes=true”;