INDEX FAST FULL SCAN AND SKIP SCAN
在快速扫描索引时,oracle读取b-tree索引的所有叶子节点块,而且是顺序读,而且可以同时读取db_file_multiblock_read_count,
而且索引快速扫描比full table scan的物理IO小很多,可以更快的响应请求。以下还是看下原文:
The fast full scan can be used if all of the columns in the query for the table are in the index
with the leading edge of the index not part of the WHERE condition.
In the following example, the emp table is used. As shown
earlier in this chapter, it has a concatenated index on the columns empno, ename, and deptno.
select empno, ename, deptno
from emp
where deptno = 30;
Since all of the columns in the SQL statement are in the index, a fast full scan is available.
Index fast full scans are commonly performed during joins in which only the indexed join key
columns are queried. As an alternative, Oracle may perform a skip-scan access of the index; the
optimizer should consider the histogram for the Deptno column (if one is available) and decide
which of the available access paths yields the lowest possible performance cost.
既然这里提到了SKIP SCANS,顺别看一下,SKIP SCAN比FAST FULL SCAN INDEX 更快
为了好理解,就拿书中的例子看一下:
这里emp5表有N 万行,建议一个组合索引,如果在查询中不使用引导列作为查询条件,也就是where后面不能有JOB列的限制
优化器为CBO,引导列JOB最好没有重复值,可以强制使用index
create index skip1 on emp5(job,empno);
select count(*)
from emp5
where empno = 7900;
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=5)
1 0 SORT (AGGREGATE)
2 1 INDEX (FAST FULL SCAN) OF 'SKIP1' (NON-UNIQUE)
Statistics
6826 consistent gets
6819 physical reads
select /*+ index(emp5 skip1) */ count(*)
from emp5
where empno = 7900;
Elapsed: 00:00:00.56
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=1 Bytes=5)
1 0 SORT (AGGREGATE)
2 1 INDEX (SKIP SCAN) OF 'SKIP1' (NON-UNIQUE)
Statistics
21 consistent gets --------逻辑IO相比较而言小了很多
17 physical reads
这里可以看到IO上的区别是很明显的,逻辑IO和物理IO小了很多,有利就有弊,使用SKIP SCAN虽然IO上是有效果,但是CPU的消耗会增加,以上就可以看出,cost变大了,不能盲目使用,以下两个例子CPU COST很明显