深入理解Oracle索引(2)：INDEX UNIQUE SCAN，INDEX FULL SCAN和INDEX FAST FULL SCAN

㈠ INDEX UNIQUE SCAN

如果表上有唯一索引, 搜索索引列时会用上INDEX UNIQUE SCAN

原来Index Unique Scan和Index Range Scan在B Tree上的搜索路径是一样的
只是Index Unique Scan在找到应该含有要找的Index Key的block后便停止了搜索，因为该键是唯一的
而Index Range Scan还要循着指针继续找下去直到条件不满足时

Oracle9i Database Performance Tuning Guide and Reference提到：
This access path is used when all columns of a unique (B-tree) index are specified with equality conditions

下面测试一下这句话的真实性：

hr@ORCL> create table t (id number,name varchar2(10));
hr@ORCL> create unique index ind_t on t (id);
hr@ORCL> insert into t values(1,'a');
hr@ORCL> insert into t values(2,'b');
hr@ORCL> commit;
hr@ORCL> set autot trace exp
hr@ORCL> select * from t where id=1;

Execution Plan
----------------------------------------------------------
Plan hash value: 1366100657

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    20 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |    20 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | IND_T |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
hr@ORCL> drop index ind_t;
hr@ORCL> create index ind_t on t (id,name);
hr@ORCL> analyze index ind_t compute statistics；
hr@ORCL> select * from t where id=1;

Execution Plan
----------------------------------------------------------
Plan hash value: 3131770069

--------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |     1 |    20 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IND_T |     1 |    20 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------

由此，B-Tree结构的unique index上的所有索引列都要被指定时，才会使用index unique scan来访问

㈡ INDEX FULL SCAN和 INDEX FAST FULL SCAN

在一句SQL中，如果我们想搜索的列都包含在索引里面的话
那么Index Full Scan 和 Index Fast Full Scan 都可以被采用代替Full Table Scan
有索引排序的时候,优化器可能会偏向于Index Full Scan

如果Select 列表中数据都可来自于索引中包含的字段，则通常容易选择Index Fast Full Scan
这样出来的数据是根据索引 的 extent 为单元，无顺序的
扫描block包含了所有枝节点，不区分是否叶子节点。可以做多块顺序扫描，一个io 包含多个block

Index Full Scan 是根据索引的叶子节点顺序获取叶子节点信息然后获得数据（通常还去表中获取数据）
这样出来的数据是有序的，并且除了定位第一个叶子节点走了根和枝节点block外
其他叶子节点只由上一个叶子节点的next 指针获得，这样一个索引叶子block就可产生一个io，是离散读

10g在Index Full Scan阶段就把不符合记录的数据先给过滤掉了,最后只有符合条件的rowid做回表
而9i下,Oracle就比较傻了,Index Full Scan的阶段实际上什么也没做
只是把所有的rowid都给得到,然后根据得到的rowid从表中取出数据,再过滤掉不符合条件的记录

INDEX_FFS使用多块读，所以在大部分时候,INDEX_FFS速度应该会比单块读的INDEX_FS要快

Index Full Scan将会按逻辑的顺序访问各个索引节点，结果集已经按照索引键值顺序排序，不需要单独排序
Index Fast Full Scan时索引块通过多块进行读取，结果集是无序的
所以，如果结果集需要排序那会使用INDEX_FS,如果结果集不需要排序则会使用INDEX_FFS


INDEX FULL SCAN
HINT写法：INDEX(表名 索引名)或者 INDEX_FS(表名 索引名）
原理：Oracle定位到索引的ROOT BLOCK，然后到BRANCH BLOCK（如果有的话），再定位到第一个LEAF BLOCK
然后根据LEAF BLOCK的双向链表顺序读取。它所读取的块都是有顺序的，也是经过排序的


INDEX FAST FULL SCAN
HINT写法：INDEX_FFS(表名 索引名）
原理：从段头开始，读取包含位图块，ROOT BLOCK，所有的BRANCH BLOCK，LEAF BLOCK
读取的顺序完全有物理存储位置决定，并采取多块读，每次读取DB_FILE_MULTIBLOCK_READ_COUNT个块
IFFS的成本计算公式是：
leaf_blocks/k （k依赖于db_file_multiblock_read_count，leaf_blocks依赖于索引统计信息）
查询某个表记录总数的时候，往往基于PRIMARY KEY的INDEX FAST FULL SCAN是最有效的

sys@ORCL> select index_name,blevel,leaf_blocks from user_indexes where table_name=upper('T');

INDEX_NAME                         BLEVEL LEAF_BLOCKS
------------------------------ ---------- -----------
IDX_T                                   1         282

FAST是多块读，结果集无顺序，如果需要排序则会多一步sort order by；FULL SCAN是单块读，有顺序，可避免ORDER BY成本
Performance Tuning Guide中描述如下：
Index Full Scan可以避免排序操作，在如下情况下，优化器将使用IFS
1.若谓词引用了索引中的字段
2.若查询中的所有字段都包含在索引中，并且索引字段中至少有一个字段非空

Index Fast Full Scan
查询中的所有字段都包含在索引中且至少一个索引列有非空约束的情况下，才会进行IFFS
IFFS只访问索引，不需要访问数据表，通过读取多个数据块的方式读取整个索引，并不是根据索引的键值进行排序
如果加个ORDER BY，则FAST会变成IFS，如果索引中没有完全包含要查询的列，或可能INDEX FULL SCAN，或可能直接访问表
IFFS通过读取多个数据块的方式读取整个索引，且可进行并行处理，相关数据并不是根据索引的键值进行排序
因此，速度比IFS快，但它不能用于避免排序操作