查询转换——视图合并（1）！

最新推荐文章于 2025-01-15 09:20:37 发布

原创最新推荐文章于 2025-01-15 09:20:37 发布 · 1.9k 阅读

0 ·

CC 4.0 BY-SA版权

execution plan 专栏收录该内容

27 篇文章

订阅专栏

本文详细探讨了SQL查询转换的目的、视图合并的概念及其对执行计划的影响，通过具体示例展示了如何调整查询以优化性能。重点分析了在不同查询转换策略下（如禁止合并、强制合并等）查询执行效率的变化，提供了实用的优化技巧。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

查询转换的主要目的就是确定如果改变查询的写法会不会提供更好的查询计划。查询转换能够并且可能会重写你的查询。查询转换器可能会改变你最初所写查询的结构，只要这样的改变不会影响结果集。

视图合并是一种将内嵌或存储试视图展开为能够独立分析或者与查询剩余部分合并成总体执行计划的独立查询块的转换。

/*+ NO_QUERY_TRANSFORMATION */ 禁止查询转换。

/*+ MERGE(TABLE) */ 能够对视图的各个查询进行相应的合并。
/*+ NO_MERGE(TABLE) */ 对于有可合并的视图不再合并。

QL> select /*+ gather_plan_statistics */
  2   e.empno, e.ename, e.sal, v.avg_sal
  3    from emp e,
  4         (select emp.deptno, round(avg(emp.sal), 2) avg_sal
  5            from emp
  6           group by emp.deptno) v
  7   where e.deptno = v.deptno
  8     and e.sal > v.avg_sal;

     EMPNO ENAME             SAL    AVG_SAL
---------- ---------- ---------- ----------
      7499 ALLEN            1600    1566.67
      7566 JONES            2975       2175
      7698 BLAKE            2850    1566.67
      7788 SCOTT            3000       2175
      7839 KING             5000    2916.67
      7902 FORD             3000       2175

已选择6行。

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  1hjw7xx9h3fr2, child number 0
-------------------------------------
select /*+ gather_plan_statistics */  e.empno, e.ename, e.sal,
v.avg_sal   from emp e,        (select emp.deptno, round(avg(emp.sal),
2) avg_sal           from emp          group by emp.deptno) v  where
e.deptno = v.deptno    and e.sal > v.avg_sal

Plan hash value: 269884559

------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |      1 |        |      6 |00:00:00.01 |      15 |       |       |          |
|*  1 |  HASH JOIN           |      |      1 |      1 |      6 |00:00:00.01 |      15 |   951K|   951K|  605K (0)|
|   2 |   VIEW               |      |      1 |      3 |      3 |00:00:00.01 |       7 |       |       |          |
|   3 |    HASH GROUP BY     |      |      1 |      3 |      3 |00:00:00.01 |       7 |   778K|   778K| 1179K (0)|
|   4 |     TABLE ACCESS FULL| EMP  |      1 |     14 |     14 |00:00:00.01 |       7 |       |       |          |
|   5 |   TABLE ACCESS FULL  | EMP  |      1 |     14 |     14 |00:00:00.01 |       8 |       |       |          |
------------------------------------------------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------

   1 - access("E"."DEPTNO"="V"."DEPTNO")
       filter("E"."SAL">"V"."AVG_SAL")


已选择26行。

上面是默认的执行计划，使用VIEW关键字来表明视图是保持原样的。

SQL> set linesize 150
SQL> set serveroutput off;
SQL> select /*+ gather_plan_statistics no_merge(v) */
  2   e.empno, e.ename, e.sal, v.avg_sal
  3    from emp e,
  4         (select emp.deptno, round(avg(emp.sal), 2) avg_sal
  5            from emp
  6           group by emp.deptno) v
  7   where e.deptno = v.deptno
  8     and e.sal > v.avg_sal;

     EMPNO ENAME             SAL    AVG_SAL
---------- ---------- ---------- ----------
      7499 ALLEN            1600    1566.67
      7566 JONES            2975       2175
      7698 BLAKE            2850    1566.67
      7788 SCOTT            3000       2175
      7839 KING             5000    2916.67
      7902 FORD             3000       2175

已选择6行。

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  48rcfq057xujt, child number 0
-------------------------------------
select /*+ gather_plan_statistics no_merge(v) */  e.empno, e.ename,
e.sal, v.avg_sal   from emp e,        (select emp.deptno,
round(avg(emp.sal), 2) avg_sal           from emp          group by
emp.deptno) v  where e.deptno = v.deptno    and e.sal > v.avg_sal

Plan hash value: 269884559

------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |      1 |        |      6 |00:00:00.02 |      15 |       |       |          |
|*  1 |  HASH JOIN           |      |      1 |      1 |      6 |00:00:00.02 |      15 |   951K|   951K|  598K (0)|
|   2 |   VIEW               |      |      1 |      3 |      3 |00:00:00.01 |       7 |       |       |          |
|   3 |    HASH GROUP BY     |      |      1 |      3 |      3 |00:00:00.01 |       7 |   778K|   778K| 1187K (0)|
|   4 |     TABLE ACCESS FULL| EMP  |      1 |     14 |     14 |00:00:00.01 |       7 |       |       |          |
|   5 |   TABLE ACCESS FULL  | EMP  |      1 |     14 |     14 |00:00:00.01 |       8 |       |       |          |
------------------------------------------------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------

   1 - access("E"."DEPTNO"="V"."DEPTNO")
       filter("E"."SAL">"V"."AVG_SAL")


已选择26行。

使用no_merge跟默认情况下是一样的执行计划。

SQL>  e.empno, e.ename, e.sal, v.avg_sal
select /*+ gather_plan_statistics merge(v) */
  2   e.empno, e.ename, e.sal, v.avg_sal
  3    from emp e,
  4         (select emp.deptno, round(avg(emp.sal), 2) avg_sal
  5            from emp
  6           group by emp.deptno) v
  7   where e.deptno = v.deptno
  8     and e.sal > v.avg_sal;

     EMPNO ENAME             SAL    AVG_SAL
---------- ---------- ---------- ----------
      7788 SCOTT            3000       2175
      7566 JONES            2975       2175
      7698 BLAKE            2850    1566.67
      7839 KING             5000    2916.67
      7902 FORD             3000       2175
      7499 ALLEN            1600    1566.67

已选择6行。

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  g3r5cu2hg0h1a, child number 0
-------------------------------------
select /*+ gather_plan_statistics merge(v) */  e.empno, e.ename, e.sal,
v.avg_sal   from emp e,        (select emp.deptno, round(avg(emp.sal),
2) avg_sal           from emp          group by emp.deptno) v  where
e.deptno = v.deptno    and e.sal > v.avg_sal

Plan hash value: 2435006919

------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |      1 |        |      6 |00:00:00.01 |      14 |       |       |          |
|*  1 |  FILTER              |      |      1 |        |      6 |00:00:00.01 |      14 |       |       |          |
|   2 |   HASH GROUP BY      |      |      1 |      4 |     14 |00:00:00.01 |      14 |   736K|   736K| 1023K (0)|
|*  3 |    HASH JOIN         |      |      1 |     65 |     70 |00:00:00.01 |      14 |   791K|   791K|  614K (0)|
|   4 |     TABLE ACCESS FULL| EMP  |      1 |     14 |     14 |00:00:00.01 |       7 |       |       |          |
|   5 |     TABLE ACCESS FULL| EMP  |      1 |     14 |     14 |00:00:00.01 |       7 |       |       |          |
------------------------------------------------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------

   1 - filter("E"."SAL">ROUND(SUM("EMP"."SAL")/COUNT("EMP"."SAL"),2))
   3 - access("E"."DEPTNO"="EMP"."DEPTNO")


已选择26行。

可以看见这里执行计划和前面不一样了，注意A-Rows列，可以看出成本比前面视图不转换高，所以CBO还是聪明的，选择了对的执行计划。