跳跃式索引(Skip Scan Index)浅析

核心提示：再做几个有趣的试验，下面的试验条件是不满足SS的，跳跃式索引(Skip Scan Index)浅析(3)，但是请注意查询返回列队查询计划的影响：SQL> truncate table test1;Table truncated.SQL> begin2 for i in 1..100000 loop3 ins

再做几个有趣的试验，下面的试验条件是不满足SS的，但是请注意查询返回列队查询计划的影响：

SQL> truncate table test1;

Table truncated.

SQL> begin

2 for i in 1..100000 loop

3 insert into test1 values (i, to_char(i), to_char(i));

4 end loop;

5 commit;

6 end;

7 /

PL/SQL procedure successfully completed.

SQL> analyze table test1 compute statistics;

Table analyzed.

SQL> select * from test1

2 where b = '500';

A B C

---------- ---------- ----------

500 500 500

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=37 Card=1 Bytes=19)

1 0 TABLE ACCESS (FULL) OF 'TEST1' (Cost=37 Card=1 Bytes=19)

改变返回列：

SQL> select count(*) from test1

2 where b = '500';

COUNT(*)

----------

1

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=34 Card=1 Bytes=10)

1 0 SORT (AGGREGATE)

2 1 INDEX (FAST FULL SCAN) OF 'TEST_IDX1' (NON-UNIQUE) (Cost

=34 Card=1 Bytes=10)

再改变一种：

SQL> select a from test1

2 where b = '500';

A

----------

500

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=34 Card=1 Bytes=14)

1 0 INDEX (FAST FULL SCAN) OF 'TEST_IDX1' (NON-UNIQUE) (Cost=3

4 Card=1 Bytes=14)

使用RBO呢？

SQL> select /*+rule*/a from test1

2 where b = '500';

A

----------

500

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=HINT: RULE

1 0 TABLE ACCESS (FULL) OF 'TEST1'

值得一提的是，上述任何一个例子在8i中执行的话，都不会使用到索引（无论是否符合SS的条件）。