In oracle, the default index information should be recorded by oracle CBO, and CBO used the index to decrease the selective cost compare with the order executed plan. But is it truth and best that query sentence using indexes by CBO
Usually, we will encounter one sql which would be run quickly by creating one fit index, but this index will decrease the performance for order sqls. That is not useful for increasing one sql to decreasing more than two sqls. We also test the index which is 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
--------