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

核心提示：再取第一列distinct number为36：SQL> truncate table test1;Table truncated.SQL> begin2 for i in 1..100000 loop3 insert into test1 values (mod(i,36), to_char(i), to

再取第一列distinct number为36：

SQL> truncate table test1;

Table truncated.

SQL> begin

2 for i in 1..100000 loop

3 insert into test1 values (mod(i,36), 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 where b = '500';

A B C

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

32 500 500

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=12 Card=1 Bytes=17)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST1' (Cost=12 Card=1 B

ytes=17)

2 1 INDEX (SKIP SCAN) OF 'TEST_IDX1' (NON-UNIQUE) (Cost=37 C

ard=1)

从上面试验结果看，FTS的cost是37。当第一列distinct number小于这个值时，Oracle选择了SS。

继续试验：

SQL> select count(*) from test1

2 where b <= '1';

COUNT(*)

----------

1

Execution Plan

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

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

1 0 SORT (AGGREGATE)

2 1 INDEX (SKIP SCAN) OF 'TEST_IDX1' (NON-UNIQUE) (Cost=37 C

ard=1 Bytes=10)

注意：在b中’10’是比’1’大的最小值（char(10)类型）

SQL> select count(*) from test1

2 where b <= '10';

COUNT(*)

----------

2

Execution Plan

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

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

1 0 SORT (AGGREGATE)

2 1 TABLE ACCESS (FULL) OF 'TEST1' (Cost=37 Card=773 Bytes=7

730)

观察结果，这时候影响的因素是cardinality了。第二个查询计划中的cardinality值（773）正是b<=’10’的cardinality值：

SQL> set autotrace off

SQL> select 100000*(to_number('31302020202020202020', 'xxxxxxxxxxxxxxxxxxxx')-to

_number('31202020202020202020', 'xxxxxxxxxxxxxxxxxxxx'))/(to_number('39393939392

020202020', 'xxxxxxxxxxxxxxxxxxxx')-to_number('31202020202020202020', 'xxxxxxxxx

xxxxxxxxxxx'))+1 from dual;

100000*(TO_NUMBER('31302020202020202020','XXXXXXXXXXXXXXXXXXXX')-TO_NUMBER('3120

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

772.791768

再看一个含有第一列条件的等效的语句：

SQL> set autotrace on explain

SQL> select count(*) from test1

2 where a>=0

3 and b <='1';

COUNT(*)

----------

1

Execution Plan

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

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

1 0 SORT (AGGREGATE)

2 1 INDEX (SKIP SCAN) OF 'TEST_IDX1' (NON-UNIQUE) (Cost=37 C

ard=1 Bytes=12)