WEB开发网
开发学院数据库Oracle 跳跃式索引(Skip Scan Index)浅析 阅读

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

 2007-06-02 16:03:42 来源:WEB开发网   
核心提示:再做几个有趣的试验,下面的试验条件是不满足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的条件)。

上一页  1 2 3 

Tags:跳跃式 索引 Skip

编辑录入:爽爽 [复制链接] [打 印]
赞助商链接