好多误区

为了准备面试，好好地恶补了一下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少就用哪种，完全没有定论。

=====

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