查询转换-视图合并

-- 查询转换
查询转换发生在进行到执行计划的开发过程之前，进行完语法和权限检查之后。优化器为了决定最终的执行计划而为不同的计划计算成本预估之前。
在查询通过了语法和权限检查之后，查询就进入了转换为一系列查询块的转换阶段。
查询块是通过SELECT关键字来定义的。


-- 视图合并常常发生在当外部查询块的谓语包含下列项的时候。
1.能够在另一个查询块的索引中使用的例
2.能够在另一个查询块的分区截断中所使用的例
3.在一个联结视图中能够限制返回行数的条件。


注意：以下情况会阻止视图合并的发生：
1.如果一个查询块包含解析函数或聚合函数
2.集合运算（如UNION、INTERSERCT、MINUS）
3.ORDER BY 子句或者使用了ROWNUM


以上情况可以通过使用MERGE来强制执行视图合并。但是结果集可能会与原来的查询结果不同。
视图合并行为是通过一个隐藏参数_complex_view_merging来控制的。在Oracle9及后续版本中该参数的默认值为TRUE。从Oracle 10版本开始，
转制后的查询将会由优化器进行复查，视图合并以及不合并的查询计划所需成本都会被评估。然后优化器就会选择成本最低的执行计划。
set line 300
set pages 100
set autotrace traceonly


--a. 进行视图合并
select *
  from emp a
       ,( select deptno 
            from dept ) b
 where a.deptno = b.deptno(+)
   and a.job = 'CLERK' ;


执行计划
----------------------------------------------------------
Plan hash value: 438843259


------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |     3 |   123 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS OUTER|         |     3 |   123 |     3   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| EMP     |     3 |   114 |     3   (0)| 00:00:01 |
|*  3 |   INDEX UNIQUE SCAN| PK_DEPT |     1 |     3 |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------


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


   2 - filter("A"."JOB"='CLERK')
   3 - access("A"."DEPTNO"="DEPTNO"(+))




统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         10  consistent gets
          1  physical reads
          0  redo size
       1257  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          4  rows processed


-- b.没有进行视图合并              
select *
  from emp a
       ,( select  /*+ no_merge*/deptno 
            from dept ) b
 where a.deptno = b.deptno(+)
   and a.job = 'CLERK' ;


执行计划
----------------------------------------------------------
Plan hash value: 1029234253


-----------------------------------------------------------------------------------
| Id  | Operation               | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |         |     3 |   153 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS OUTER     |         |     3 |   153 |     3   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL     | EMP     |     3 |   114 |     3   (0)| 00:00:01 |
|   3 |   VIEW PUSHED PREDICATE |         |     1 |    13 |     0   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN    | PK_DEPT |     1 |     3 |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------


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


   2 - filter("A"."JOB"='CLERK')
   4 - access("DEPTNO"="A"."DEPTNO")


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         10  consistent gets
          0  physical reads
          0  redo size
       1257  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          4  rows processed                  
          
-- c.无法进行视图合并          
-- ORDER BY          
select *
  from emp a
       ,( select deptno
                 ,avg(sal)  avg_sal 
            from emp
           group by deptno ) b
 where a.deptno = b.deptno
   and a.sal >= b.avg_sal ;       
执行计划
----------------------------------------------------------
Plan hash value: 269884559


-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     1 |    64 |     8  (25)| 00:00:01 |
|*  1 |  HASH JOIN           |      |     1 |    64 |     8  (25)| 00:00:01 |
|   2 |   VIEW               |      |     3 |    78 |     4  (25)| 00:00:01 |
|   3 |    HASH GROUP BY     |      |     3 |    21 |     4  (25)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| EMP  |    14 |    98 |     3   (0)| 00:00:01 |
|   5 |   TABLE ACCESS FULL  | EMP  |    14 |   532 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------


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


   1 - access("A"."DEPTNO"="B"."DEPTNO")
       filter("A"."SAL">="B"."AVG_SAL")




统计信息
----------------------------------------------------------
         24  recursive calls
          0  db block gets
         17  consistent gets
          0  physical reads
          0  redo size
       1503  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          6  rows processed