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”;