查询转换方式

查询转换方式有很多种：
1、视图合并(view merge )      2、子查询展开( subquery unnesting )        3、谓词推入 (predicate pushdown )    


1、视图合并：就是视图的基表和外部表做连接
  创建一个视图
create view v1 as
select * from emp where deptno = 10;

select *
  from v1, dept
 where v1.deptno = dept.deptno
   and v1.job = 'CLERK';

找到v1的基表emp，做了视图合并.emp和dept返回的记录很少，所以走嵌套循环和hash都很正常

不让他进行视图合并的方法：

《1》/*+ no_meger(view)*/ 
《2》查询块包含分析函数或聚合函数、集合运算（例如union、intersect、minus）,
order by字句或者使用了rownum，视图合并将会被禁止或者限制。

rownum也会阻止谓词推入

2、（1）、走子查询展开：就是where子句后边有in，not in，exists，not exists，<,<=,>,>=等，CBO认为对SQL进行等价改写以后能够更好的进行优化，等价改写的过程就是子查询展开

SQL> select ename from emp where deptno in (select deptno from dept where dname like 'S');

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

--------------------------------------------------------------------------------
| Id  | Operation           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |          |     5 |   110 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN SEMI     |          |     5 |   110 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL | EMP      |    14 |   126 |     2   (0)| 00:00:01 |
|   3 |   VIEW              | VW_NSO_1 |     1 |    13 |     2   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| DEPT     |     1 |    12 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------

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


   1 - access("DEPTNO"="$nso_col_1")
   4 - filter("DNAME" LIKE 'S')


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




（2）、加一个Hint禁止走子查询展开，只要有filter exists一定没有走子查询展开，


filter是嵌套表有多少不重复数据就会返回多少条记录。


SQL> select ename from emp where deptno in (select /*+ no_unnest*/ deptno from dept where dname like 'S');

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


---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     5 |    45 |     8   (0)| 00:00:01 |
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   126 |     2   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| DEPT |     1 |    12 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------


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


   1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "DEPT" "DEPT"
              WHERE "DEPTNO"=:B1 AND "DNAME" LIKE 'S'))
   3 - filter("DEPTNO"=:B1 AND "DNAME" LIKE 'S')

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


or是阻止进行子查询展开的条件。

查询名字带S的用户或者empno>5000的人

（1）、
SQL> select ename from emp where deptno in (select deptno from dept where dname like 'S') or empno >= 5000 ;


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

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    14 |   182 |     2   (0)| 00:00:01 |
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   182 |     2   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| DEPT |     1 |    12 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------


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


   1 - filter("EMPNO">=5000 OR  EXISTS (SELECT 0 FROM "DEPT" "DEPT"
              WHERE "DEPTNO"=:B1 AND "DNAME" LIKE 'S'))
   3 - filter("DEPTNO"=:B1 AND "DNAME" LIKE 'S')




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

（2）、改写后的SQL语句：


SQL> select ename from emp where deptno in (select deptno from dept where dname like 'S')
  2  union all
  3  select ename from emp where empno >= 5000 ;

ENAME
--------------------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
h

已选择15行。

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


---------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |    19 |   250 |     7  (43)| 00:00:01 |
|   1 |  UNION-ALL           |          |       |       |            |          |
|*  2 |   HASH JOIN SEMI     |          |     5 |   110 |     5  (20)| 00:00:01 |
|   3 |    TABLE ACCESS FULL | EMP      |    14 |   126 |     2   (0)| 00:00:01 |
|   4 |    VIEW              | VW_NSO_1 |     1 |    13 |     2   (0)| 00:00:01 |
|*  5 |     TABLE ACCESS FULL| DEPT     |     1 |    12 |     2   (0)| 00:00:01 |
|*  6 |   TABLE ACCESS FULL  | EMP      |    14 |   140 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------


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

   2 - access("DEPTNO"="$nso_col_1")
   5 - filter("DNAME" LIKE 'S')
   6 - filter("EMPNO">=5000)

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

3、谓词推进（ predicate pushdown ）
不让他做视图合并，加一个hint/*+ no_merge(v1) */ 
select /*+ no_merge(v1) */ *
  from v1, dept
 where v1.deptno = dept.deptno
   and v1.job = 'CLERK';

原本执行计划应该是，先进行表连接，然后再筛选结果，但是，这里*4将“DEPTNO"=10 AND "JOB"='CLERK'
这个条件推入到视图里，先进行过滤然后进行合并。