SQL优化——必须掌握的查询变换

1、子查询非嵌套

子查询非嵌套（Subquery Unnesting）：当where子查询中有in、not in、exists、not exists等，CBO会尝试将子查询展开（unnest），从而消除FILTER，这个过程就叫作子查询非嵌套。子查询非嵌套的目的就是消除FILTER。

为什么要消除FILTER呢？因为FILTER的驱动表是固定的，一旦驱动表被固定，那么执行计划也就被固定了。对于DBA来说这并不是好事，因为一旦固定的执行计划本身是错误的（低效的），就会引起性能问题，想要提升性能必须改写SQL语句，但是这时SQL已经上线，无法更改，所以，一定要消除FILTER。

很多公司都有开发DBA，开发DBA很大一部分的工作职责就是：必须保证SQL上线之后，每个SQL语句的执行计划都是可控的，这样才能尽可能避免系统中SQL越跑越慢。

大家可能会问，既然能通过HINT(NO_UNNEST)让执行计划产生FILTER，那么执行计划中如果产生了FILTER，能否通过HINT(UNNEST)消除FILTER呢？执行计划中的FILTER很少能够通过HINT消除，一般需要通过SQL等价改写来消除。

再次强调：执行计划中如果产生了FILTER，一般是无法通过HINT消除的，一定要注意执行计划中的FILTER。

请注意，虽然我们一直强调要消除执行计划中的FILTER，本意是要保证执行计划是可控的，并不意味着执行计划产生了FILTER就一定性能差，相反有时候我们还可以用FILTER来优化SQL。

哪些SQL写法容易产生FILTER呢？当子查询语句含有exists或者not exists时，子查询中有固化子查询关键词（union/union all/start with connect by/rownum/cube/rollup），那么执行计划中就容易产生FILTER，例如，exists中有rownum产生FILTER：

SQL> select ename, deptno
  2    from emp
  3   where  exists (select deptno
  4            from dept
  5           where loc = 'CHICAGO'
  6             and dept.deptno = emp.deptno
  7             and rownum <= 1);

6 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3414630506

-------------------------------------------------------------------------------------
| Id |Operation                     | Name    | Rows  | Bytes | Cost (%CPU)|Time    |
-------------------------------------------------------------------------------------
|  0 |SELECT STATEMENT              |         |     5 |    45 |     6   (0)|00:00:01|
|* 1 | FILTER                       |         |       |       |            |        |
|  2 |  TABLE ACCESS FULL           | EMP     |    14 |   126 |     3   (0)|00:00:01|
|* 3 |  COUNT STOPKEY               |         |       |       |            |        |
|* 4 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    11 |     1   (0)|00:00:01|
|* 5 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)|00:00:01|
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter( EXISTS (SELECT 0 FROM "DEPT" "DEPT" WHERE ROWNUM<=1 AND
              "DEPT"."DEPTNO"=:B1 AND "LOC"='CHICAGO'))
   3 - filter(ROWNUM<=1)
   4 - filter("LOC"='CHICAGO')
   5 - access("DEPT"."DEPTNO"=:B1)

为什么exists/not exists容易产生FILTER，而in很少会产生FILTER呢？当子查询中有固化关键字（union/union all/start with connect by/rownum/cube/rollup），子查询会被固化为一个整体，采用exists/not exists这种写法，这时子查询中有主表连接列，只能是主表通过连接列传值给子表，所以CBO只能选择FILTER。而我们如果将SQL改写为in/not in这种写法，子查询虽然被固化为整体，但是子查询中没有主表连接列字段，这个时候CBO就不会选择FILTER。

2、视图合并

视图合并（View Merge）：当SQL语句中有内联视图（in-line view，from后面的子查询），或者SQL语句中有用create view创建的视图，CBO会尝试将内联视图/视图拆开，进行等价的改写，这个过程就叫作视图合并。如果没有发生视图合并，在执行计划中，我们可以看到VIEW关键字，而且视图/子查询会作为一个整体。如果发生了视图合并，那么视图/子查询就会被拆开，而且执行计划中视图/子查询部分就没有VIEW关键字。

现有如下SQL及其执行计划（Oracle11.2.0.1）：

SQL> select a.*, c.grade
  2    from (select ename, sal, a.deptno, b.dname
  3            from emp a, dept b
  4           where a.deptno = b.deptno) a,
  5         salgrade c
  6   where a.sal between c.losal and c.hisal;

14 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3095952880

------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |     1 |    65 |     9  (23)|
|   1 |  NESTED LOOPS                |          |       |       |            |
|   2 |   NESTED LOOPS               |          |     1 |    65 |     9  (23)|
|   3 |    MERGE JOIN                |          |     1 |    52 |     8  (25)|
|   4 |     SORT JOIN                |          |     5 |   195 |     4  (25)|
|   5 |      TABLE ACCESS FULL       | SALGRADE |     5 |   195 |     3   (0)|
|*  6 |     FILTER                   |          |       |       |            |
|*  7 |      SORT JOIN               |          |    14 |   182 |     4  (25)|
|   8 |       TABLE ACCESS FULL      | EMP      |    14 |   182 |     3   (0)|
|*  9 |    INDEX UNIQUE SCAN         | PK_DEPT  |     1 |       |     0   (0)|
|  10 |   TABLE ACCESS BY INDEX ROWID| DEPT     |     1 |    13 |     1   (0)|
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - filter("SAL"<="C"."HISAL")
   7 - access("SAL">="C"."LOSAL")
       filter("SAL">="C"."LOSAL")
   9 - access("A"."DEPTNO"="B"."DEPTNO")

SQL语句中有内联视图，但是执行计划中没有VIEW关键字，说明发生了视图合并。内联视图中EMP表是与DEPT表关联的，但是执行计划中，EMP表是与SALGRADE先关联的，EMP表与SALGRADE关联之后得到一个结果集，再与DEPT表进行的关联，这说明发生了视图合并之后，有可能会打乱视图/子查询中表的原本连接顺序。

现在我们添加HINT:no_merge（子查询别名/视图别名）禁止视图合并，再看执行计划：

SQL> select / *+ no_merge(a) */
  2   a.*, c.grade
  3    from (select ename, sal, a.deptno, b.dname
  4            from emp a, dept b
  5           where a.deptno = b.deptno) a,
  6         salgrade c
  7   where a.sal between c.losal and c.hisal;

14 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 4110645763

----------------------------------------------------------------------------------
| Id  | Operation                        | Name     | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |          |     1 |    81 |    11  (28)|
|   1 |  MERGE JOIN                      |          |     1 |    81 |    11  (28)|
|   2 |   SORT JOIN                      |          |     5 |   195 |     4  (25)|
|   3 |    TABLE ACCESS FULL             | SALGRADE |     5 |   195 |     3   (0)|
|*  4 |   FILTER                         |          |       |       |            |
|*  5 |    SORT JOIN                     |          |    14 |   588 |     7  (29)|
|   6 |     VIEW                         |          |    14 |   588 |     6  (17)|
|   7 |      MERGE JOIN                  |          |    14 |   364 |     6  (17)|
|   8 |       TABLE ACCESS BY INDEX ROWID| DEPT     |     4 |    52 |     2   (0)|
|   9 |        INDEX FULL SCAN           | PK_DEPT  |     4 |       |     1   (0)|
|* 10 |       SORT JOIN                  |          |    14 |   182 |     4  (25)|
|  11 |        TABLE ACCESS FULL         | EMP      |    14 |   182 |     3   (0)|
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("A"."SAL"<="C"."HISAL")
   5 - access("A"."SAL">="C"."LOSAL")
       filter("A"."SAL">="C"."LOSAL")
  10 - access("A"."DEPTNO"="B"."DEPTNO")
       filter("A"."DEPTNO"="B"."DEPTNO")

执行计划中有VIEW关键字，而且EMP是与DEPT进行关联的，这说明执行计划中没有发生视图合并。

当视图/子查询中有多个表关联，发生视图合并之后一般会将视图/子查询内部表关联顺序打乱。

大家可能遇到过类似案例，例如下面SQL所示：

select ... from () a,() b where a.id=b.id;

单独执行子查询a，速度非常快，单独执行子查询b，速度也非常快，但是把上面两个子查询组合在一起，速度反而很慢，这就是典型的视图合并引起的性能问题。遇到类似问题，我们可以添加HINT:no_merge禁止视图合并，也可以让子查询a与子查询b进行HASH连接，当子查询a与子查询b进行HASH连接之后，就不会发生视图合并了。

select / *+ use_hash(a,b) */ ... from () a,() b where a.id=b.id;

为什么让子查询a与子查询b进行HASH连接能使SQL变快呢？大家再回忆一下HASH连接的算法，嵌套循环会传值（驱动表传值给被驱动表，通过连接列），HASH连接不会传值。因为HASH连接不传值，所以当子查询a与子查询b进行HASH连接之后，会自动地把子查询a与子查询b作为一个整体。

与子查询非嵌套一样，当视图中有固化子查询关键字的时候，就不能发生视图合并。固化子查询的关键字包括union、union all、start with connect by、rownum、cube、rollup。

3、谓词推入

谓词推入（Pushing Predicate）：当SQL语句中包含不能合并的视图，同时视图有谓词过滤（也就是where过滤条件），CBO会将谓词过滤条件推入视图中，这个过程就叫作谓词推入。谓词推入的主要目的就是让Oracle尽可能早地过滤掉无用的数据，从而提升查询性能。

为什么谓词推入必须要有不能被合并的视图呢？因为一旦视图被合并了，执行计划中根本找不到视图，这个时候谓词往哪里推呢？所以谓词推入的必要前提是SQL中要有不能合并的视图。

一般情况下，常量的谓词推入对性能的提升都是有益的。那么什么是常量的谓词推入呢？常量的谓词推入就是谓词是正常的过滤条件，而非连接列。

还有一种谓词推入，是把连接列当作谓词推入到视图中，这种谓词推入我们一般叫作连接列谓词推入，此类谓词推入最容易产生性能问题。

大家在工作中，如果遇到执行计划中VIEW PUSHED PREDICATE一定要注意，如果SQL执行很快，不用理会；如果SQL执行很慢，可以先关闭连接列谓词推入（alter session set “_push_join_predicate” = false）功能，再逐步分析为什么连接列谓词推入之后，SQL性能很差。连接列谓词推入性能变差一般是CBO将驱动表Rows计算错误（算少），导致视图作为嵌套循环被驱动表，然后一直反复被扫描；也有可能是视图太过复杂，视图本身存在性能问题，这时需要单独优化视图。例如视图单独执行耗时1秒，在进行谓词推入之后，视图会被扫描多次，假设扫描1 000次，每次执行时间从1秒提升到了0.5秒，但是视图被执行了1 000次，总的耗时反而多了，这时谓词推入反而降低性能。

一定要注意，当视图中有rownum会导致无法谓词推入，所以一般情况下，我们不建议在视图中使用rownum。为什么rownum会导致无法谓词推入呢？这是因为当谓词推入之后，rownum的值已经发生改变，已经改变了SQL结果集，任何查询变换必须是在不改变SQL结果集的前提下才能进行。