日期:2014-05-16 浏览次数:20710 次
下面测试一下这句话的真实性:
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 | --------------------------------------
㈡ 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
&n