07-优化学习-查询转换

第7章 查询转换

7.1、子查询非嵌套

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

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

样例

TEST01@testora>set lines 200
select ename, deptno
  from scott.emp
 where exists (select deptno
          from scott.dept
         where dname = 'CHICAGO'
           and emp.deptno = dept.deptno
        union
        select deptno
          from scott.dept
         where loc = 'CHICAGO'
 11             and dept.deptno = emp.deptno);

6 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2705207488

-----------------------------------------------------------------------------------------
| Id  | Operation		       | Name	 | Rows  | Bytes | Cost (%CPU)| Time	 |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	       |	 |     5 |    45 |    15  (40)| 00:00:01 |
|*  1 |  FILTER 		       |	 |	 |	 |	      | 	 |
|   2 |   TABLE ACCESS FULL	       | EMP	 |    14 |   126 |     3   (0)| 00:00:01 |
|   3 |   SORT UNIQUE		       |	 |     2 |    48 |     4  (50)| 00:00:01 |
|   4 |    UNION-ALL		       |	 |	 |	 |	      | 	 |
|*  5 |     TABLE ACCESS BY INDEX ROWID| DEPT	 |    1 |    13 |     1   (0)| 00:00:01 |
|*  6 |      INDEX UNIQUE SCAN	       | PK_DEPT |     1 |	 |     0   (0)| 00:00:01 |
|*  7 |     TABLE ACCESS BY INDEX ROWID| DEPT	 |    1 |    11 |     1   (0)| 00:00:01 |
|*  8 |      INDEX UNIQUE SCAN	       | PK_DEPT |     1 |	 |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   1 - filter( EXISTS ( (SELECT "DEPTNO" FROM "SCOTT"."DEPT" "DEPT" WHERE
	      "DEPT"."DEPTNO"=:B1 AND "DNAME"='CHICAGO')UNION (SELECT "DEPTNO" FROM
	      "SCOTT"."DEPT" "DEPT" WHERE "DEPT"."DEPTNO"=:B2 AND "LOC"='CHICAGO')))
   5 - filter("DNAME"='CHICAGO')
   6 - access("DEPT"."DEPTNO"=:B1)
   7 - filter("LOC"='CHICAGO')
   8 - access("DEPT"."DEPTNO"=:B1)


Statistics
----------------------------------------------------------
	  0  recursive calls
	  0  db block gets
	 19  consistent gets
	  0  physical reads
	  0  redo size
	714  bytes sent via SQL*Net to client
	519  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  3  sorts (memory)
	  0  sorts (disk)
	  6  rows processed
改写SQL，消除FILTER：(/*+ no_unnest */ , /*+ unnset */)

select ename, deptno
  from scott.emp
 where exists (select 1
          from (select deptno
                  from scott.dept
                 where dname = 'CHICAGO'
                union
                select deptno
                  from scott.dept
                 where loc = 'CHICAGO') a
 11           where a.deptno = emp.deptno);

6 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 4243948922

------------------------------------------------------------------------------
| Id  | Operation	      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |	   5 |	 110 |	  11  (19)| 00:00:01 |
|*  1 |  HASH JOIN SEMI       |      |	   5 |	 110 |	  11  (19)| 00:00:01 |
|   2 |   TABLE ACCESS FULL   | EMP  |	  14 |	 126 |	   3   (0)| 00:00:01 |
|   3 |   VIEW		      |      |	   2 |	  26 |	   8  (25)| 00:00:01 |
|   4 |    SORT UNIQUE	      |      |	   1 |	  24 |	   8  (25)| 00:00:01 |
|   5 |     UNION-ALL	      |      |	     |	     |		  |	     |
|*  6 |      TABLE ACCESS FULL| DEPT |	   1 |	  13 |	   3   (0)| 00:00:01 |
|*  7 |      TABLE ACCESS FULL| DEPT |	   1 |	  11 |	   3   (0)| 00:00:01 |
------------------------------------------------------------------------------

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

   1 - access("A"."DEPTNO"="EMP"."DEPTNO")
   6 - filter("DNAME"='CHICAGO')
   7 - filter("LOC"='CHICAGO')


Statistics
----------------------------------------------------------
	  1  recursive calls
	  0  db block gets
	 18  consistent gets
	  0  physical reads
	  0  redo size
	714  bytes sent via SQL*Net to client
	519  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  1  sorts (memory)
	  0  sorts (disk)
	  6  rows processed

7.2、视图合并（view merge）

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

*/*+ no_merge /

7.3、谓词推入（pushing predicate）

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

关闭连接谓词推入：
alter session set "_push_join_predicate" = false ;