好多误区

Wayne posted @ Thu, 16 Jun 2011 17:49:02 +0000 in Experience , 2791 readers

为了准备面试,好好地恶补了一下PLSQL,连带着Oracle的一些机制。对于过去遗留的问题,也详细查找实验了一番,结果发现好多误区和讹传。可能那些说法在刚出来的时候是对的吧,但是随着版本的发展,很多早就已经不适用了。可惜网络上还是充斥着这些内容。有些是过去的遗留,还有很多是听信了那些过时结论又没实验,而却当作“答案”新近告诉别人的,这种影响极为恶劣。一看日期,人家都还以为现在这个时代依然如此呢,相当容易误导。

 

首先先说一个常见的关于复合索引的认识。很多地方关于复合索引都有这么种说法:复合索引按建立时排的顺序排在最前面的是引导列,当查询条件是引导列在最前时,走索引,否则不走;如果多个复合索引列在查询条件中顺序乱了也不走;如果中间跳过一个,我看到两种说法,一种说不走,一种说可以跳跃着走索引。

这样描述可能不清楚,举个实际例子:

CREATE TABLE tb( a number, b number, c number);

CREATE INDEX idx ON tb(a,b,c);

以上说法认为:

select * from tb where a=1 and b=1 and c=1   -->走索引

select * from tb where b=1                              -->不走索引,无引导列

select * from tb where a=1 and b=1                --->走索引

select * from tb where a=1 and c=1                 --->有的说走,有的说不走

select * from tb where a=1 and c=1 and b=1   --->不走,顺序乱

select * from tb where b=1 and c=1                --->不走,无引导列

但是,在我实验的Oracle 11g Release2上,经过测试,以上写法全部会走索引。唯一的区别在于条件中有引导列a参与的查询,无论a出现在where后面第一个的位置还是别的位置,采用的是

INDEX RANGE SCAN,而没有查询条件中没有a的,使用的是INDEX FULL SCAN。

 

关于索引还有很多说法,其中有一条是 is not null 和 is null 不会走索引。这个我今天在itpub上发现已经有人纠正过了,可见这个帖子的八楼。单列B-Tree索引是不储存null的,这点正是is not null和is null不走索引的出处,但是既然索引里面存的都没有null的,那么索引里必然都是is not null的,所以is not null完全是可以走索引的,并且实测结果也正是如此。而关于is null,那帖子已经也说的很清楚,在B-Tree复合索引或者bitMap中还是能用的。

 

然后是一个广为流传的关于IN和EXISTS效率的争论。之所以说是争论是因为已经有很多人已经发现了问题,作了很多修正。那种坚持认为" EXISTS "效率就是比“IN”高的已经很少了。现在比较流行的说法是,EXISTS会使用NESTED LOOPS,而IN使用的是HASH JOIN。当然,如果数据量过大,或者两张表差距太大,或者有排序之类的,那么最后结果还是很难说的,HASH JOIN或者MERGE SORT JOIN都有可能。

我今天则建了几张很小的表进行测试,测试结果没有验证上面的任何一种说法。我用了IN,EXISTS,右外连接后过滤,NOT IN, NOT EXISTS分别做的测试,每次测试结果都是HASH JOIN ANTI NA。在连接字段上加了索引后,NOT EXISTS变成了NESTED LOOPS ANTI,IN和EXISTS变成了NESTED LOOPS SEMI, NOT IN 因为没法使用索引,依然是HASH JOIN ANTI,右外连接则是HASH JOIN OUTER。而我加了排序之后,这些连接方式也没变,只是又额外多了一步SORT ORDER BY罢了。对此我的结论是,除了外联接这个方式之外,其他各种IN,EXISTS,以至于内连接,都是标准的CBO作风,哪种COST少就用哪种,完全没有定论。

 

=====

今天就先写到这,这篇文章打算长期更新,把发现的误区都逐个收集进去。被误导真的很痛苦,本来就有不懂的地方才要去查,查到的还是对不上情况的…… 以后遇到事情还是得多做实验验证,不可轻信。

 


Login *


loading captcha image...
(type the code from the image)
or Ctrl+Enter