Statistics
----------------------------------------------------------
1088 recursive calls
0 db block gets
164 consistent gets
23 physical reads
0 redo size
822 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
12 sorts (memory)
0 sorts (disk)
1 rows processed
从上述执行计划的内容可以看到，执行计划走的是索引唯一性扫描，消耗的逻辑读为164.
第二步骤，我们删除唯一性索引idx_empno
SQL> DROP INDEX IDX_EMPNO;
创建非唯一性的B树索引。
SQL> CREATE INDEX IDX_EMPNO ON TEST(EMPNO);
Index dropped.
再次收集统计信息
SQL> begin
2 dbms_stats.gather_table_stats('SCOTT','TEST',estimate_percent=>100,cascade=>true,method_opt=>'for all columns size 1');
3 end;
4 /
PL/SQL procedure successfully completed.
再次清空buffer cache 和shared pool,千万别在生产环境中执行。
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;
SQL> select * from test where empno=7369;
Execution Plan
----------------------------------------------------------
Plan hash value: 1320605699
--------------------------------------------------------------------------------
---------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| T
ime |
--------------------------------------------------------------------------------
---------
| 0 | SELECT STATEMENT | | 1 | 37 | 2 (0)| 0
0:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 37 | 2 (0)| 0
0:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_EMPNO | 1 | | 1 (0)| 0
0:00:01 |
--------------------------------------------------------------------------------
---------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7369)
Statistics
----------------------------------------------------------
813 recursive calls
0 db block gets
165 consistent gets
27 physical reads
0 redo size
822 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
9 sorts (memory)
0 sorts (disk)
1 rows processed
从执行计划的内容看，sql的执行计划从之前的索引唯一性扫描变成索引范围扫描，逻辑读的值由164变为了165，比原来多扫描1次。
3.索引全扫描
索引全扫描(INDEX FULL SCAN)适用于所有的B树索引。索引全扫描要扫描索引所有叶子块的所有索引。在默认情况下，索引全扫描从左到右依次顺序扫描索引所有叶子块的所有索引，索引是有序，所以索引全扫描执行的结果也是有序的。
SQL> select empno from test
2 ;