跳跃式索引(Skip Scan Index)浅析
2007-06-02 16:03:42 来源:WEB开发网再做几个有趣的试验,下面的试验条件是不满足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的条件)。
更多精彩
赞助商链接