[翻译]Oracle中的增强子查询优化

newkid · 发表于 2012-10-15 23:18

谢谢指正，确实漏写了两个字，我已经修改过来了。

1、2.3节中， Q5 为什么能展开成Q6，感觉Q6是一个很奇怪的查询。
首先EXISTS可以转换成INNER JOIN:

SELECT * FROM A
WHERE EXISTS (SELECT 1 FROM B WHERE A.KEY = B.KEY)

可以转换成：
SELECT A.*
FROM A , (SELECT DISTINCT B.KEY FROM B) B2
WHERE A.KEY=B2.KEY;

注意转换后的第二个集合在连接键上必须是唯一的，可以用DISTINCT或GROUP BY来实现，这样连接后才不会导致一行A数据变成多行的情况。

也可以转换成：
SELECT A.*
FROM A , (SELECT DISTINCT A.ROWID RID FROM A,B WHERE A.KEY = B.KEY) B2
WHERE A.ROWID=B2.RID;

第二种写法把外面的表推入子查询，类似Q6.
Q5原来是针对L1的每一行都执行子查询。Q6把外面的lineitem(L1)推进去后改名LX, 和L2做连接，这样就避开了和外层的相关性，从而避免了逐行操作。
然后它只留下符合要求的那些ROWID, 利用这些ROWID来过滤最外面的lineitem，即外面的那个INNER JOIN。

2、6.2节中，第3点为什么要建立第二个hash 表？

你要理解反连接的工作原理，它先搜索匹配，一旦匹配上，则说明左表的这一行必须丢弃。
如果左表的列都非空，这就很好理解了，比如(1,2,3), 它只要在第二表中搜索(1,2,3)，如果搜得到就丢弃，搜不到就返回。
问题出在左表数据行上的空列。例子中的(NULL,2,3)，你不能光在第二表中搜索(NULL,2,3)。即使第二表中不存在(NULL,2,3)，也不能下结论说左表的这行可以返回。因为如果第二表存在(1,2,3), 那么这也被认为是一种“匹配”：1和NULL的匹配结果是“未知”，所以一旦(1,2,3)存在，则左表的(NULL,2,3)必须被丢弃。但是我们一开始只是建立右表的三个连接列的HASH表，即(C1,C2,C3)的HASH表，这样的结构只能匹配所有列，你不能利用该HASH表来判断(C2,C3)是否存在。C1+C2+C3和C2+C3的HASH结果是完全没有关系的，可能差了十万八千里。
这时候就必须再次引入另一个HASH表，这个HASH表包含了右表所有(C2,C3)的HASH结果。以后一旦碰到(NULL, X, Y)这样的数据，这第二个HASH表都能用上。
有可能这引入的第二个HASH表还不够，因为左表中还可能有(X,NULL,Y)这样的数据，如果碰到了就得在(C1,C3)上再次建立第三个HASH表。

mdkii · 发表于 2012-10-16 21:04

如果我没理解错的话：
Q6:  SELECT s_name
  FROM supplier, lineitem L1,
   (SELECT LX.rowid xrowid
      FROM lineitem L2, lineitem LX
      WHERE L1.l_suppkey <> LX.l_suppkey AND
            L1.l_orderkey = LX.l_orderkey
   GROUP BY LX.rowid
   HAVING SUM(CASE WHEN L2.l_receiptdate > L2.l_commitdate
                     THEN 1
                     ELSE 0
                  END) = 0) V
WHERE  s_suppkey = L1.l_suppkey AND
      L1.rowid = V.xrowid;
这里的L1难道不应该是L2？

另外 Q7里面好像也少了个条件
L1.l_suppkey <> L2.l_suppkey
，group by 里的 S.rowid 貌似也不需要。

2、你的意思是左表里如果是 (null,2,3) ，那么需要看右表里
存不存在 ( X,2,3)的记录，由于无法穷举右表里的X值，所以
索性建一个（C2,C3）列的hash 表做查找？
重建一个hash 表，再去做hash查找，貌似代价也不小啊。