EMPNO
----------
7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876
EMPNO
----------
7900
7902
7934
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 654388723
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 56 | 1 (0)| 00:00:01 |
| 1 | INDEX FULL SCAN | IDX_EMPNO | 14 | 56 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
556 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
从执行计划中，我们看到统计信息部分的“sorts(memory)”和“sorts(disk)”的值都为0.说明 SQL执行结果已经按照列empno排好序了。
4.索引快速全扫描
索引快速全扫描（INDEX FAST FULL SCAN ）和索引全扫描非常类似，它适用所有B树索引。
索引快速全扫描也要扫码所有叶子块的所有索引行。
他们区别如下：：
1)索引快速全扫描只适用于CBO
2)索引快速全扫描可以使用多块读，也可以并行执行。
3)索引快速全扫描的执行结果不一定是有序的。这是因为索引快速全扫描时，oracle是根据索引行在磁盘上的物理存储顺序来扫描的，而不是根据索引行的逻辑顺序来扫描的。
5.索引跳跃式扫描
索引跳跃式扫描（INDEX SKIP SCAN）适用所有复合B树索引。它使那些在where 条件中没有对目标索引的前导列指定查询条件但同时又对该索引的非前导列指定了查询条件的目标SQL依然可以用上该索引。这就像是在扫描索引时跳过了他的前导列，直接从该索引的非前导列开始扫描一样。
看一个例子：
SQL>create table test1(name varchar2(10),id number not null);
创建一个复合B树索引
SQL>create index idx_naid on test1(name,id);
插入10000条记录，5000条name列为test,5000条name列为prot
begin
for i in 1..5000 loop
insert into test1 values('test',i);
end loop;
commit;
end;
/
begin
for i in 1..5000 loop
insert into test1 values('prot',i);
end loop;
commit;
end;
/
SQL>SET AUTOTRACE TRACEONLY
SQL>exec dbms_stats.gather_table_stats('SCOTT','TEST1',estimate_percent=>100,cascade=>true,method_opt=>'for all columns size 1');
SQL>select * from test1 where id=200;
Execution Plan
----------------------------------------------------------
Plan hash value: 4123651466
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 16 | 3 (0)| 00:00:01 |
|* 1 | INDEX SKIP SCAN | IDX_NAID | 2 | 16 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ID"=200)
filter("ID"=200)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
505 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed