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

最新推荐文章于 2024-01-24 15:48:10 发布
最新推荐文章于 2024-01-24 15:48:10 发布
阅读量610 收藏
点赞数
分类专栏： [数据库SQL优化] 文章标签： oracle 视图合并
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/tanzuai/article/details/42237607
版权

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

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

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

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

[sql]  view plain copy print ?
  1. QL> select /*+ gather_plan_statistics */  
  2.   2   e.empno, e.ename, e.sal, v.avg_sal  
  3.   3    from emp e,  
  4.   4         (select emp.deptno, round(avg(emp.sal), 2) avg_sal  
  5.   5            from emp  
  6.   6           group by emp.deptno) v  
  7.   7   where e.deptno = v.deptno  
  8.   8     and e.sal > v.avg_sal;  
  9.   
  10.      EMPNO ENAME             SAL    AVG_SAL  
  11. ---------- ---------- ---------- ----------  
  12.       7499 ALLEN            1600    1566.67  
  13.       7566 JONES            2975       2175  
  14.       7698 BLAKE            2850    1566.67  
  15.       7788 SCOTT            3000       2175  
  16.       7839 KING             5000    2916.67  
  17.       7902 FORD             3000       2175  
  18.   
  19. 已选择6行。  
  20.   
  21. SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));  
  22.   
  23. PLAN_TABLE_OUTPUT  
  24. ------------------------------------------------------------------------------------------------------------------------------------------------------  
  25. SQL_ID  1hjw7xx9h3fr2, child number 0  
  26. -------------------------------------  
  27. select /*+ gather_plan_statistics */  e.empno, e.ename, e.sal,  
  28. v.avg_sal   from emp e,        (select emp.deptno, round(avg(emp.sal),  
  29. 2) avg_sal           from emp          group by emp.deptno) v  where  
  30. e.deptno = v.deptno    and e.sal > v.avg_sal  
  31.   
  32. Plan hash value: 269884559  
  33.   
  34. ------------------------------------------------------------------------------------------------------------------  
  35. | Id  | Operation            | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |  
  36.   
  37. PLAN_TABLE_OUTPUT  
  38. ------------------------------------------------------------------------------------------------------------------------------------------------------  
  39. ------------------------------------------------------------------------------------------------------------------  
  40. |   0 | SELECT STATEMENT     |      |      1 |        |      6 |00:00:00.01 |      15 |       |       |          |  
  41. |*  1 |  HASH JOIN           |      |      1 |      1 |      6 |00:00:00.01 |      15 |   951K|   951K|  605K (0)|  
  42. |   2 |   VIEW               |      |      1 |      3 |      3 |00:00:00.01 |       7 |       |       |          |  
  43. |   3 |    HASH GROUP BY     |      |      1 |      3 |      3 |00:00:00.01 |       7 |   778K|   778K| 1179K (0)|  
  44. |   4 |     TABLE ACCESS FULL| EMP  |      1 |     14 |     14 |00:00:00.01 |       7 |       |       |          |  
  45. |   5 |   TABLE ACCESS FULL  | EMP  |      1 |     14 |     14 |00:00:00.01 |       8 |       |       |          |  
  46. ------------------------------------------------------------------------------------------------------------------  
  47.   
  48. Predicate Information (identified by operation id):  
  49. ---------------------------------------------------  
  50.   
  51. PLAN_TABLE_OUTPUT  
  52. ------------------------------------------------------------------------------------------------------------------------------------------------------  
  53.   
  54.    1 - access("E"."DEPTNO"="V"."DEPTNO")  
  55.        filter("E"."SAL">"V"."AVG_SAL")  
  56.   
  57.   
  58. 已选择26行。  

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

[sql]  view plain copy print ?
  1. SQL> set linesize 150  
  2. SQL> set serveroutput off;  
  3. SQL> select /*+ gather_plan_statistics no_merge(v) */  
  4.   2   e.empno, e.ename, e.sal, v.avg_sal  
  5.   3    from emp e,  
  6.   4         (select emp.deptno, round(avg(emp.sal), 2) avg_sal  
  7.   5            from emp  
  8.   6           group by emp.deptno) v  
  9.   7   where e.deptno = v.deptno  
  10.   8     and e.sal > v.avg_sal;  
  11.   
  12.      EMPNO ENAME             SAL    AVG_SAL  
  13. ---------- ---------- ---------- ----------  
  14.       7499 ALLEN            1600    1566.67  
  15.       7566 JONES            2975       2175  
  16.       7698 BLAKE            2850    1566.67  
  17.       7788 SCOTT            3000       2175  
  18.       7839 KING             5000    2916.67  
  19.       7902 FORD             3000       2175  
  20.   
  21. 已选择6行。  
  22.   
  23. SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));  
  24.   
  25. PLAN_TABLE_OUTPUT  
  26. ------------------------------------------------------------------------------------------------------------------------------------------------------  
  27. SQL_ID  48rcfq057xujt, child number 0  
  28. -------------------------------------  
  29. select /*+ gather_plan_statistics no_merge(v) */  e.empno, e.ename,  
  30. e.sal, v.avg_sal   from emp e,        (select emp.deptno,  
  31. round(avg(emp.sal), 2) avg_sal           from emp          group by  
  32. emp.deptno) v  where e.deptno = v.deptno    and e.sal > v.avg_sal  
  33.   
  34. Plan hash value: 269884559  
  35.   
  36. ------------------------------------------------------------------------------------------------------------------  
  37. | Id  | Operation            | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |  
  38.   
  39. PLAN_TABLE_OUTPUT  
  40. ------------------------------------------------------------------------------------------------------------------------------------------------------  
  41. ------------------------------------------------------------------------------------------------------------------  
  42. |   0 | SELECT STATEMENT     |      |      1 |        |      6 |00:00:00.02 |      15 |       |       |          |  
  43. |*  1 |  HASH JOIN           |      |      1 |      1 |      6 |00:00:00.02 |      15 |   951K|   951K|  598K (0)|  
  44. |   2 |   VIEW               |      |      1 |      3 |      3 |00:00:00.01 |       7 |       |       |          |  
  45. |   3 |    HASH GROUP BY     |      |      1 |      3 |      3 |00:00:00.01 |       7 |   778K|   778K| 1187K (0)|  
  46. |   4 |     TABLE ACCESS FULL| EMP  |      1 |     14 |     14 |00:00:00.01 |       7 |       |       |          |  
  47. |   5 |   TABLE ACCESS FULL  | EMP  |      1 |     14 |     14 |00:00:00.01 |       8 |       |       |          |  
  48. ------------------------------------------------------------------------------------------------------------------  
  49.   
  50. Predicate Information (identified by operation id):  
  51. ---------------------------------------------------  
  52.   
  53. PLAN_TABLE_OUTPUT  
  54. ------------------------------------------------------------------------------------------------------------------------------------------------------  
  55.   
  56.    1 - access("E"."DEPTNO"="V"."DEPTNO")  
  57.        filter("E"."SAL">"V"."AVG_SAL")  
  58.   
  59.   
  60. 已选择26行。  

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

[sql]  view plain copy print ?
  1. SQL>  e.empno, e.ename, e.sal, v.avg_sal  
  2. select /*+ gather_plan_statistics merge(v) */  
  3.   2   e.empno, e.ename, e.sal, v.avg_sal  
  4.   3    from emp e,  
  5.   4         (select emp.deptno, round(avg(emp.sal), 2) avg_sal  
  6.   5            from emp  
  7.   6           group by emp.deptno) v  
  8.   7   where e.deptno = v.deptno  
  9.   8     and e.sal > v.avg_sal;  
  10.   
  11.      EMPNO ENAME             SAL    AVG_SAL  
  12. ---------- ---------- ---------- ----------  
  13.       7788 SCOTT            3000       2175  
  14.       7566 JONES            2975       2175  
  15.       7698 BLAKE            2850    1566.67  
  16.       7839 KING             5000    2916.67  
  17.       7902 FORD             3000       2175  
  18.       7499 ALLEN            1600    1566.67  
  19.   
  20. 已选择6行。  
  21.   
  22. SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));  
  23.   
  24. PLAN_TABLE_OUTPUT  
  25. ------------------------------------------------------------------------------------------------------------------------------------------------------  
  26. SQL_ID  g3r5cu2hg0h1a, child number 0  
  27. -------------------------------------  
  28. select /*+ gather_plan_statistics merge(v) */  e.empno, e.ename, e.sal,  
  29. v.avg_sal   from emp e,        (select emp.deptno, round(avg(emp.sal),  
  30. 2) avg_sal           from emp          group by emp.deptno) v  where  
  31. e.deptno = v.deptno    and e.sal > v.avg_sal  
  32.   
  33. Plan hash value: 2435006919  
  34.   
  35. ------------------------------------------------------------------------------------------------------------------  
  36. | Id  | Operation            | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |  
  37.   
  38. PLAN_TABLE_OUTPUT  
  39. ------------------------------------------------------------------------------------------------------------------------------------------------------  
  40. ------------------------------------------------------------------------------------------------------------------  
  41. |   0 | SELECT STATEMENT     |      |      1 |        |      6 |00:00:00.01 |      14 |       |       |          |  
  42. |*  1 |  FILTER              |      |      1 |        |      6 |00:00:00.01 |      14 |       |       |          |  
  43. |   2 |   HASH GROUP BY      |      |      1 |      4 |     14 |00:00:00.01 |      14 |   736K|   736K| 1023K (0)|  
  44. |*  3 |    HASH JOIN         |      |      1 |     65 |     70 |00:00:00.01 |      14 |   791K|   791K|  614K (0)|  
  45. |   4 |     TABLE ACCESS FULL| EMP  |      1 |     14 |     14 |00:00:00.01 |       7 |       |       |          |  
  46. |   5 |     TABLE ACCESS FULL| EMP  |      1 |     14 |     14 |00:00:00.01 |       7 |       |       |          |  
  47. ------------------------------------------------------------------------------------------------------------------  
  48.   
  49. Predicate Information (identified by operation id):  
  50. ---------------------------------------------------  
  51.   
  52. PLAN_TABLE_OUTPUT  
  53. ------------------------------------------------------------------------------------------------------------------------------------------------------  
  54.   
  55.    1 - filter("E"."SAL">ROUND(SUM("EMP"."SAL")/COUNT("EMP"."SAL"),2))  
  56.    3 - access("E"."DEPTNO"="EMP"."DEPTNO")  
  57.   
  58.   
  59. 已选择26行。  

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

谭祖爱
关注
专栏目录
硬盘数据恢复的十大神器————运维一定要知道！
Reyn_观极
06-24 2811
因为众所周知的原因：硬盘总是坏！ 其实硬盘数据恢复也是那么难，一起来看看有哪些神器可以帮助你快速恢复硬盘数据，避免数据丢失！ 在一切工作进行之前，请先判断硬盘是否有损坏，以及缺损类型，而往往硬盘出现问题主要集中下以下两个方面： 物理（驱动器故障或者组件故障）； 逻辑（文件系统出错或是数据损坏）。 一旦你对硬盘的问题所在有了清楚的认识，你便可以着手寻找答案了。这里列举了十个用于硬盘数据恢复的工具。它们依靠效率和效果在众多同类工具中脱颖而出。让我们一同了解一下吧！ 1：TestDisk 数据修
OpenCV-Python实战（1）——OpenCV简介与图像处理基础
热门推荐
盼小辉丶的博客
08-10 21万+
OpenCV 是一个的跨平台计算机视觉库，可以运行在 Linux、Windows 和 Mac OS 操作系统上。它轻量级而且高效——由一系列 C 函数和少量 C++ 类构成，同时也提供了 Python 接口，实现了图像处理和计算机视觉方面的很多通用算法。在本文中，将介绍 OpenCV 库，包括它的主要模块和典型应用场景，同时使用 OpenCV-Python 实战讲解图像处理基础要点。
oracle多个视图整合,SQL关联方式-视图合并 （View Merging）
weixin_30670859的博客
04-06 1944
周末时，近日有网友朋友向我提问，“什么是视图合并？多次看到这个名词，但是每次都是扎入到执行计划中去分析了，但是对这个概念和功能完全不清楚，干啥用的？” 对于这个概念，趁赶着这个问题，自己也好好复习一下。一.什么是视图合并(View Merging)？我们通过一个SQL案例来说明，现有如下SQL：SQL1:select emp1.empno, emp1.ename, emp1.commfrom em...
把动态SQL结果集转化为视图查询的方法（运行环境：SQL2005以上版本）
一个世界一个家,我爱中国!
06-10 5067
------------------------------------------------------------------------------------ Author : htl258(Tony)-- Date   : 2010-06-10 01:49:18-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) --          Jul  9 2008 14:43:34 --          Cop
简单的视图合并
24号信仰
08-07 783
简单的视图合并，案例SQL和PLAN如下： SELECT * FROM QQ_VIEW_RRRRRR_FFFFFF WHERE howtime >= 0 And inorout = 1 And Callid like '%13511232777%' AND starttime >= to_date('2017-08-06 00:49:01', 'yyyy-m...
2.7 视图合并
media_test2的博客
02-02 1735
2.7 视图合并正在更新内容，请稍后
SQL 优化 第七章 查询转换 2视图合并和谓词推入
songjian1104的专栏
06-09 469
参考资料： 本系列博客主要参考资料有CUUG冉乃纲老师数据库教学笔记，《SQL优化核心思想》（罗炳森，黄超，钟侥著），《PostgreSQL技术内幕：查询优化深度探索》（张树杰著），排名不分先后。 2视图合并和谓词推入 2.1视图合并 2.1.1 视图合并环境准备 create table tests1 as select * from dba_objects; create ta...
PostgreSQL——查询优化——预处理
weixin_47373497的博客
11-20 2711
2021SC@SDUSC 目录概述查询优化pg_plan_queries函数pg_plan_queries函数调用的—— **pg_plan_query函数**pg_plan_queries函数返回的—— **PlannedStmt结构体**pg_plan_query函数调用的——**planner函数**standard_planner函数调用的——**subquery_planner函数**subquery_planner函数返回的——**Plan结构体** 概述 我负责的PostgreSQL代码部分：
mysql 视图可以作为查询的数据源_MySQL数据库视图
weixin_39994806的博客
01-19 1760
什么是视图视图是从一个或多个表中导出来的表，是一种虚拟存在的表。视图就像一个窗口，通过这个窗口可以看到系统专门提供的数据。这样，用户可以不用看到整个数据库中的数据，而之关心对自己有用的数据。数据库中只存放了视图的定义，而没有存放视图中的数据，这些数据存放在原来的表中。使用视图查询数据时，数据库系统会从原来的表中取出对应的数据。视图中的数据依赖于原来表中的数据，一旦表中数据发生改变，显示在视图中的数...
Oceanbase查询改写：视图合并
weixin_64863780的博客
03-01 429
默认情况下，当查询语句中存在视图时，需要等待视图中的查询执行完毕后再执行父查询。这种做法使得优化器无法将视图查询和父查询作为一个整体进行优化，限制了优化的空间。为此，Oceanbase中定义了相应的视图合并规则，能够将视图查询中的查询逻辑合并到父查询中，为优化器进一步优化提供了基础。
hint oracle group by,ORACLE的视图合并HINT
weixin_33914982的博客
04-05 604
第一章ORACLE的视图合并介绍1.1视图合并首先介绍一下ORACLE的视图合并概念：视图合并(View Merging)是优化器处理带视图的目标SQL的一种优化手段，它是指优化器不再将目标SQL中视图的定义语句当做一个独立的处理单元来执行，而是会将其拆开，把其定义SQL语句的基表拿出来与外表合并，合并后的SQL将只剩下外部查询的表和原视图中的基表，不再有视图出现。视图合并的好处是让优化器有更多的...
oracle hints merge 视图合并
zhaoyangjian724的专栏
09-18 239
explain plan for select * from (select a.*, rownum num from ( select b.sumAmt,b.totalCnt,b.CHL_MER_ID as merId,base.MCHT_NAME as merName from ( select sum(TXN_AMT) as sumAmt,count(1) a...
Oracle查询转换之视图合并
weixin_33948416的博客
10-06 763
一.简单视图合并：指针对那些不含外连接，以及所带视图定义sql语句中不含distinct，group by等聚合函数的目标sql的视图合并。createorreplaceviewview_1as SELECTt2.prod_id FROMsalest2,customerst3 WHEREt2.cust_id=t3.cust_id AND...
Oracle查询转换（一）简单视图合并
cuishan5183的博客
06-12 288
视图的定义及分类参考：http://blog.itpub.net/28536251/viewspace-1871039/ 如果目标SQL中包含了视图，且该视图为一个简单视图，则优化器会将视图拆开，直接使用...
12.查询转换-视图合并技术
最新发布
weixin_43346403的博客
01-24 477
如果不做视图合并最优:/*+no_merge(view_1)*/视图合并并不总是是最优的执行计划，要视图具体情况来看。如果视图合并最优:/*+merge(view_1)*/2.简单视图合并的例子。
Oracle查询转换（三）外连接视图合并
cuishan5183的博客
06-15 221
视图的定义及分类参考：http://blog.itpub.net/28536251/viewspace-1871039/ 如果视图定义中包含外连接或者视图与目标SQL中其他表做外连接，优化器会评估视图合并后的语义是否与目...
oracle 将表转为视图,oracle的查询转化之简单视图合并及外连接视图合并
weixin_29809927的博客
04-02 419
参考：https://blogs.oracle.com/optimizer/entry/optimizer_transformations_view_merging_part_1本文演示所用的表均来自Oracle sample schemas的HR用户。视图合并：优化器不再将目标sql中视图的定义当做一个独立的处理单元来单独执行，而是将其拆开，把其定义SQL中的基表拿出来与外部查询中的表合并。这样...
基于数据挖掘的量化交易系统设计——数据预处理关键
2. **数据集成**：将来自不同来源的数据合并在一起，形成一个统一的数据视图。例如，来自新浪财经、WIND、同花顺等网站的股票和期货数据需要整合成一个完整的数据库，以便进行综合分析。 3. **数据归约**：减少数据...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

谭祖爱

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值