Execution Plan
----------------------------------------------------------
Plan hash value: 1963203073
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP1 | 1 | 38 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP1_NAME_IND | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ENAME"='SCOTT')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
867 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
不可见索引被优化器启用
5、将索引变为可见，则优化器使用索引访问
11:59:56 SCOTT@ enmo> alter index emp1_name_ind visible;
Index altered.
Elapsed: 00:00:00.06
12:00:46 SCOTT@ enmo> alter session set optimizer_use_invisible_indexes=false;
Session altered.
Elapsed: 00:00:00.00
12:00:58 SCOTT@ enmo> select * from emp1 where ename='SCOTT';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 1963203073
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP1 | 1 | 38 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP1_NAME_IND | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ENAME"='SCOTT')
Statistics
----------------------------------------------------------
189 recursive calls
0 db block gets
26 consistent gets
0 physical reads
0 redo size
867 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
6、使用hints，不可见索引也不能被启用
12:01:10 SCOTT@ enmo> ALTER INDEX emp1_name_ind invisible;
Index altered.