Oracle 常见索引扫描方式概述，哪种索引扫描最快！

一.常见的索引扫描方式

1. INDEX RANGE SCAN
2. INDEX FAST FULL SCAN
3. INDEX FULL SCAN(MIN/MAX)
4. INDEX FULL SCAN

二.分别模拟使用这些索引的场景

1.INDEX RANGE SCAN

create table t1 as select rownum as id, rownum/2 as id2 from dual connect by level<=500000;
create index idx1 on t1(id);
set linesize 200
set autotrace trace
select * from t1 where id=100;

2.INDEX FAST FULL SCAN

create table t2 as select * from dba_objects;
create index idx2 on t2(object_id);
set linesize 200
set autotrace trace
select count(*) from t where object_id is not null;

3.INDEX FULL SCAN(MIN/MAX)

create table t3 as select * from dba_objects;
create index idx3 on t3(object_id);
select max(object_id) from t3;

4.INDEX FULL SCAN

create table t as select * from dba_objects;
alter table t modify object_id not null;
update t set object_id = 2;
update t set object_id=5 where rownum<=25000;
commit;
create index idx9 on t(object_id);
select /*+index(t)*/ distinct object_id from t;

三.使用场景与速度比较

首先，说INDEX RANGE SCAN 即 索引范围扫描，我们都知道索引的高度是很低的，索引范围扫描是一种高效的扫描方式，在返回记录很少时相当高效，这也是最常见的索引扫描方式。

而 INDEX FULL SCAN(MIN/MAX)扫描也是基于索引的存储结构实现的，B Tree 的所有数据都存储在叶子节点，而叶子节点的数据是有序的，如果遇到查询最大值/最小值的场景，正常来说 INDEX FULL SCAN(MIN/MAX) 速度是最快的，因为在扫描时，只需要扫描B Tree的第一个块和最后一个块即可，这样就直接找到了最大值/最小值。

INDEX FULL SCAN和INDEX FAST FULL SCAN  

可以明确的说INDEX FAST FULL SCAN 是快于INDEX FULL SCAN 的。

原理是INDEX FAST FULL SCAN 是一次性读取多个数据块，而INDEX FULL SCAN 只是一次性读取一个数据块。那为什么还要有INDEX FULL SCAN 这样的索引扫描方式？？？   

看上面的模拟场景2就可以发现 如果想要统计select count(*)  那一次性读取多个数据块当然是最好的选择。

再看模拟场景4 select /*+index(t)*/ distinct object_id from t;

为什么选择INDEX FULL SCAN 一次性只读取一个数据块呢，原因是distinct 是有排序的操作的 如果使用INDEX FAST FULL SCAN 一次性读取多个数据块是难以保证 有序的。并且，我们使用了索引避免了排序。这在Oracle的设计中非常常见，Oracle 中 LGWR 进程是最繁忙的进程，那为什么LGWR进程 不设置为多进程呢？ 也是由于LGWR 需要保证写日志的有序性，这样才能根据顺序恢复。

但在模拟场景4 中 我使用了hint ，因为不使用hint 也是会走 INDEX FAST FULL SCAN。这是由于虽然我们使用索引，让场景四 使用了INDEX FULL SCAN。但这也带来一个问题，一次读取一个数据块造成了大量的逻辑读。 可以看到 没有使用hint 逻辑读是150，而使用了hint 逻辑读则是219。

在这个时候CBO就会进行判断

如果使用 INDEX FULL SCAN ，则肯定可以避免排序，因为数据块是一个一个读取的，但这样造成了大量的逻辑读。 

如果使用 INDEX FAST FULL SCAN，则排序是不可避免的，因为数据库是批量读取的，但这样也有一个好处，那就是减少了大量的逻辑读。

就这个场景来看，CBO 已经做除了决定，那就是使用INDEX FAST FULL SCAN ，宁愿多耗费排序的操作，也不愿意 造成大量的逻辑读，给系统造成负担。