【SQL 优化器技术系列】 Subquery “flattening”

Oracle 2005 年出了一个 30 多页的小册子，《Query Optimization in Oracle Database10g Release 2》，介绍了常见的优化器技术。

我是做 SQL 执行的，优化部分只了解皮毛，从没有系统学习过。本系列逐个学习和介绍，自我提升，也帮助他人。

子查询是很费的操作，特别是关联子查询，外面每得到一行结果，就要去重新执行一遍子查询里的 SQL。最常见的优化手段是将子查询展开，改写成 join，semi-join，anti-join 的形式。

子查询常见的形式包括：

A in (subquery)
A not in (subquery)
A = (subquery)
NOT exists (subquery)
exists (subquery)

考虑下面的查询：

SELECT D.DNAME FROM DEPT D WHERE D.DEPTNO IN (SELECT E.DEPTNO FROM EMP E WHERE E.SAL > 10000)

改写1

它可以改写成类似这样的语句：

SELECT D.DNAME FROM DEPT D, EMP E WHERE D.DEPTNO = E.DEPTNO AND E.SAL > 10000

这样就可以先把薪资大于 10000 的人过滤出来，然后做 join。不过，上面这个语句改变了原来语句的语义，它会输出多个重复的 DNAME。

SEMI JOIN 能够去掉重复行，但是没有直接的标准 SQL 语句来表示 SEMI JOIN。伪语句如下：

SELECT D.DNAME FROM DEPT D <SEMI-JOIN> EMP E ON D.DEPTNO = E.DEPTNO WHERE E.SAL > 10000

NOTE: SQL 中，并没有 SEMI JOIN 语法，通常就是用 EXISTS 语法来表示 SEMI-JOIN 语义

改写2

不使用 SEMI-JOIN 改写，改用 DISTINCT：

SELECT D.DNAME FROM (SELECT DISTINCT DEPTNO FROM EMP) E, DEPT D WHERE
 D.DEPTNO = E.DEPTNO AND E.SAL > 10000;

这个改写会对 EMP 的过滤结果做一次 UNIQUE SORT，一般来说并不高效。

---

附录
MaxCompute SEMI JOIN
MySQL Semi Joins