简单的视图合并

最新推荐文章于 2024-01-24 15:48:10 发布
最新推荐文章于 2024-01-24 15:48:10 发布
阅读量774 收藏
点赞数
分类专栏： 【SQL TUNING】 24号信仰の优化专栏 文章标签： 视图合并 view merge /*   MERGE (V) */ /*   NO_MERGE (V) */ 消除sort order by
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Skybig1988/article/details/81477434
版权

简单的视图合并，案例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-mm-dd hh24:mi:ss')
   And Starttime <= to_date('2017-08-14 23:49:01', 'yyyy-mm-dd hh24:mi:ss')
   AND Howtime >= 0
   AND ((Extention in ('20001',
                       '20002',
                       '20003',
                       '20004',
                      ......
                       '53027',
                       '53028',
                       '53029',
                       '53030',
                       '53031')) OR
       (Agent in ('55001',
                   '55002',
                   '55005',
                   '55006',
                   ......
                   '55935',
                   '55936',
                   '55937',
                   '55938',
                   '55939')))
 ORDER BY starttime DESC

Elapsed: 00:00:11.45
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
Plan hash value: 3993928168

------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                       |  5843 |  2379K|       | 11165   (1)| 00:02:14 |       |       |
|   1 |  SORT ORDER BY                           |                       |  5843 |  2379K|  2472K| 11165   (1)| 00:02:14 |       |       |
|*  2 |   HASH JOIN RIGHT OUTER                  |                       |  5843 |  2379K|       | 10646   (1)| 00:02:08 |       |       |
|   3 |    TABLE ACCESS FULL                     | QC_TTTT_RRRRR_CCCCCCC | 12108 |   378K|       |    21   (0)| 00:00:01 |       |       |
|*  4 |    HASH JOIN OUTER                       |                       |  5843 |  2196K|       | 10625   (1)| 00:02:08 |       |       |
|   5 |     NESTED LOOPS OUTER                   |                       |  5760 |  1811K|       |  7900   (1)| 00:01:35 |       |       |
|*  6 |      HASH JOIN OUTER                     |                       |  5760 |  1411K|       |   622   (1)| 00:00:08 |       |       |
|   7 |       NESTED LOOPS OUTER                 |                       |  5760 |  1220K|       |   492   (1)| 00:00:06 |       |       |
|*  8 |        TABLE ACCESS BY GLOBAL INDEX ROWID| T_SSS                 |  5760 |   990K|       |   491   (1)| 00:00:06 |    70 |    70 |
|*  9 |         INDEX RANGE SCAN                 | IDX_SSS_STTTTT        |    28 |       |       |   312   (1)| 00:00:04 |       |       |
|  10 |        TABLE ACCESS BY INDEX ROWID       | QC_SSSSSS             |     1 |    41 |       |     1   (0)| 00:00:01 |       |       |
|* 11 |         INDEX UNIQUE SCAN                | PK_QC_SSSSSS          |     1 |       |       |     0   (0)| 00:00:01 |       |       |
|  12 |       TABLE ACCESS FULL                  | QC_GGGGGGGGGGG        | 78254 |  2598K|       |   130   (1)| 00:00:02 |       |       |
|  13 |      TABLE ACCESS BY INDEX ROWID         | JJJJJJJJ              |     1 |    71 |       |     2   (0)| 00:00:01 |       |       |
|* 14 |       INDEX UNIQUE SCAN                  | PK_VIEMODELS_UUUUUUUU |     1 |       |       |     1   (0)| 00:00:01 |       |       |
|  15 |     TABLE ACCESS FULL                    | XX_SSSSS_ZZ           |  1051K|    63M|       |  2720   (1)| 00:00:33 |       |       |
------------------------------------------------------------------------------------------------------------------------------------------

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

   2 - access("UNIQUEGUID"="QC_TTTT_RRRRR_CCCCCCC"."UNIQUEID"(+))
   4 - access("UNIQUEID"="CALLID"(+))
   6 - access("UNIQUEGUID"="QC_GGGGGGGGGGG"."UNIQUEID"(+))
   8 - filter("INOROUT"=1 AND "VOICEPATH" IS NOT NULL AND (("EXTENTION"='20001' OR "EXTENTION"='20002' OR "EXTENTION"='20003' OR
              ......"EXTENTION"='53026' OR "EXTENTION"='53027' OR "EXTENTION"='53028' OR "EXTENTION"='53029' O)
   9 - access("STARTTIME">=TO_DATE(' 2017-08-06 00:49:01', 'syyyy-mm-dd hh24:mi:ss') AND "STARTTIME"<=TO_DATE(' 2017-08-14
              23:49:01', 'syyyy-mm-dd hh24:mi:ss'))
       filter("CALLID" LIKE '%13511232777%')
  11 - access("UNIQUEGUID"="QC_SSSSSS"."UNIQUEID"(+))
  14 - access("UNIQUEGUID"="JJJJJJJJ"."UNIQUEID"(+))

SQL执行了11s，返回3条数据，QQ_VIEW_RRRRRR_FFFFFF是一个view，因权限关系无法获取其源码

因无法获取视图元数据，能用的信息只有执行计划。查一下执行计划中的表的详细信息

T_SSS4GB2000W
XX_SSSSS_ZZ80MB 100W  
QC_TTTT_RRRRR_CCCCCCC0.56MB1W 
QC_GGGGGGGGGGG4MB 7W 

 

 

 

 

 

这个SQL的是去掉sort order by 之后SQL毫秒级响应！SQL只返回了3条数据，对这3条数据排序带来的消耗完全可以忽略不计！

为何加不加ORDER BY的区别如此之大？

去掉排序之后的执行计划
Elapsed: 00:00:00.02
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3169504018

---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                       |  5843 |  2379K| 10647   (1)| 00:02:08 |       |       |
|*  1 |  HASH JOIN RIGHT OUTER                  |                       |  5843 |  2379K| 10647   (1)| 00:02:08 |       |       |
|   2 |   TABLE ACCESS FULL                     | QQ_VIEW_RRRRRR_FFFFFF | 12108 |   378K|    21   (0)| 00:00:01 |       |       |
|*  3 |   HASH JOIN OUTER                       |                       |  5843 |  2196K| 10626   (1)| 00:02:08 |       |       |
|   4 |    NESTED LOOPS OUTER                   |                       |  5760 |  1811K|  7901   (1)| 00:01:35 |       |       |
|*  5 |     HASH JOIN OUTER                     |                       |  5760 |  1411K|   623   (1)| 00:00:08 |       |       |
|   6 |      NESTED LOOPS OUTER                 |                       |  5760 |  1220K|   493   (1)| 00:00:06 |       |       |
|*  7 |       TABLE ACCESS BY GLOBAL INDEX ROWID| T_SSS                 |  5760 |   990K|   492   (1)| 00:00:06 |    70 |    70 |
|*  8 |        INDEX RANGE SCAN                 | IDX_SSS_STTTTT        |    28 |       |   313   (1)| 00:00:04 |       |       |
|   9 |       TABLE ACCESS BY INDEX ROWID       | QC_SSSSSS             |     1 |    41 |     1   (0)| 00:00:01 |       |       |
|* 10 |        INDEX UNIQUE SCAN                | PK_QC_SSSSSS          |     1 |       |     0   (0)| 00:00:01 |       |       |
|  11 |      TABLE ACCESS FULL                  | QC_GGGGGGGGGGG        | 78254 |  2598K|   130   (1)| 00:00:02 |       |       |
|  12 |     TABLE ACCESS BY INDEX ROWID         | JJJJJJJJ              |     1 |    71 |     2   (0)| 00:00:01 |       |       |
|* 13 |      INDEX UNIQUE SCAN                  | PK_VIEMODELS_UUUUUUUU |     1 |       |     1   (0)| 00:00:01 |       |       |
|  14 |    TABLE ACCESS FULL                    | XX_SSSSS_ZZ           |  1051K|    63M|  2720   (1)| 00:00:33 |       |       |
---------------------------------------------------------------------------------------------------------------------------------

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

   1 - access("UNIQUEGUID"="QQ_VIEW_RRRRRR_FFFFFF"."UNIQUEID"(+))
   3 - access("UNIQUEID"="CALLID"(+))
   5 - access("UNIQUEGUID"="QC_GGGGGGGGGGG"."UNIQUEID"(+))
   7 - filter("INOROUT"=1 AND "VOICEPATH" IS NOT NULL AND (("EXTENTION"='20001' OR "EXTENTION"='20002' OR
              "EXTENTION"='20003' OR "EXTENTION"='20004' OR "EXTENTION"='20005' OR "EXTENTION"='20006' OR "EXTENTION"='20007' OR
              "......"EXTENTION"='53025' OR "EXTENTION"='53026' OR "EXTENTION"='53027' OR "EXTENTION"='53028' OR "EXTENTION"='53029' O)
   8 - access("STARTTIME">=TO_DATE(' 2017-08-06 00:49:01', 'syyyy-mm-dd hh24:mi:ss') AND "STARTTIME"<=TO_DATE('
              2017-08-14 23:49:01', 'syyyy-mm-dd hh24:mi:ss'))
       filter("CALLID" LIKE '%13511232777%')
  10 - access("UNIQUEGUID"="QC_SSSSSS"."UNIQUEID"(+))
  13 - access("UNIQUEGUID"="JJJJJJJJ"."UNIQUEID"(+))

排序是SQL语句中最后的一步操作。不过根据我自己的理解，优化器在处理ORDER BY的时候并不是先过滤之后再ORDER BY，而是ORDER BY之后再去做其他操作。根据常识推，优化器对有序数据的使用连接方式（NEST LOOP、HASH、SORT MERGE JOIN）性能都优于无序数据，所以优化器会优先ORDER BY，也就是对于排序字段所在的表T_SYS先排序在做了后面的操作，所以11s几乎全部用在大表的排序上了【以上分析完全属于个人推测，没有文档支撑】

所以只需要优化浪费在排序上面的操作即可！ 常用的消除sort order by的手段就是建索引。因为索引存储的时候已经排过序。

但是这里排序字段上本来就有索引，为何plan中仍然存在sort order by？因为OUTER JOIN

尝试别的方法，通过SQL语句限制，先过滤 再排序，而这个case中过滤之后只剩3条数据，再排序。排序几乎不会产生任何cost

QQ_VIEW_RRRRRR_FFFFFF是一个视图，而我们在执行计划中没有匹配到！说明这个视图被合并了（展开），如果强制视图不合并（作为一个整体），常量条件推进去，这时候SQL就会优先返回视图的结果集，即3条语句，再order by这三条数据，问题可破！

使用/*+ no_merge(QQ_VIEW_RRRRRR_FFFFFF) */ 强制视图不合并，作为一个整体，整个SQL不到1s响应，得到的执行计划如下：

Elapsed: 00:00:00.31

Execution Plan
----------------------------------------------------------
Plan hash value: 3496577580

-------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name                  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |                       |  5616 |  7919K|       | 12065   (1)| 00:02:25 |       |       |
|   1 |  SORT ORDER BY                            |                       |  5616 |  7919K|  9000K| 12065   (1)| 00:02:25 |       |       |
|   2 |   VIEW                                    | QC_VIEW_RECORD_FORVIE |  5616 |  7919K|       | 10364   (1)| 00:02:05 |       |       |
|*  3 |    HASH JOIN OUTER                        |                       |  5616 |  2281K|       | 10364   (1)| 00:02:05 |       |       |
|   4 |     NESTED LOOPS OUTER                    |                       |  5537 |  1908K|       |  7639   (1)| 00:01:32 |       |       |
|*  5 |      HASH JOIN OUTER                      |                       |  5537 |  1524K|       |   644   (1)| 00:00:08 |       |       |
|*  6 |       HASH JOIN RIGHT OUTER               |                       |  5537 |  1340K|       |   513   (1)| 00:00:07 |       |       |
|   7 |        TABLE ACCESS FULL                  | QQ_VIEW_RRRRRR_FFFFFF | 12108 |   378K|       |    21   (0)| 00:00:01 |       |       |
|   8 |        NESTED LOOPS OUTER                 |                       |  5537 |  1167K|       |   492   (1)| 00:00:06 |       |       |
|*  9 |         TABLE ACCESS BY GLOBAL INDEX ROWID| T_SSS                 |  5537 |   946K|       |   491   (1)| 00:00:06 |    70 |    70 |
|* 10 |          INDEX RANGE SCAN                 | IDX_SSS_STTTTT        |    42 |       |       |   312   (1)| 00:00:04 |       |       |
|  11 |         TABLE ACCESS BY INDEX ROWID       | QC_SSSSSS             |     1 |    41 |       |     1   (0)| 00:00:01 |       |       |
|* 12 |          INDEX UNIQUE SCAN                | PK_QC_SSSSSS          |     1 |       |       |     0   (0)| 00:00:01 |       |       |
|  13 |       TABLE ACCESS FULL                   | QC_GGGGGGGGGGG        | 78254 |  2598K|       |   130   (1)| 00:00:02 |       |       |
|  14 |      TABLE ACCESS BY INDEX ROWID          | JJJJJJJJ              |     1 |    71 |       |     2   (0)| 00:00:01 |       |       |
|* 15 |       INDEX UNIQUE SCAN                   | PK_VIEMODELS_UUUUUUUU |     1 |       |       |     1   (0)| 00:00:01 |       |       |
|  16 |     TABLE ACCESS FULL                     | XX_SSSSS_ZZ           |  1051K|    63M|       |  2720   (1)| 00:00:33 |       |       |
-------------------------------------------------------------------------------------------------------------------------------------------

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

   3 - access("UNIQUEID"="CALLID"(+))
   5 - access("UNIQUEGUID"="QC_GGGGGGGGGGG"."UNIQUEID"(+))
   6 - access("UNIQUEGUID"="QQ_VIEW_RRRRRR_FFFFFF"."UNIQUEID"(+))
   9 - filter("INOROUT"=1 AND "VOICEPATH" IS NOT NULL AND "VOICESIZE">0 AND (("EXTENTION"='20001' OR "EXTENTION"='20002' OR 
              "EXTENTION"='53025' OR "EXTENTION"='53026' OR "EXTENTION"='53027' OR "EXTENTION"='53028' OR "EX)
  10 - access("STARTTIME">=TO_DATE(' 2017-08-06 00:49:01', 'syyyy-mm-dd hh24:mi:ss') AND "STARTTIME"<=TO_DATE(' 2017-08-14
              23:49:01', 'syyyy-mm-dd hh24:mi:ss'))
       filter("CALLID" LIKE '%13511232777%')
  12 - access("UNIQUEGUID"="QC_SSSSSS"."UNIQUEID"(+))
  15 - access("UNIQUEGUID"="JJJJJJJJ"."UNIQUEID"(+))

在上面的执行计划中可以看到VIEW关键字 和 视图名QQ_VIEW_RRRRRR_FFFFFF，说明视图被当做一个整体来处理！

 

24号信仰
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
SQL优化之视图合并
的专栏
06-02 1763
下面两个脚本的写法得到的结果是相同的。但是聚合的过程一个发生在外部一个发生在内部，执行过程是大不相同的，其运行的效率也自然不同。 脚本1： select t1.meterial_id ,t2.play_cnt ,t2.play_device_cnt from t_material as t1 inner join ( select track_id ...
oracle多个视图整合,SQL关联方式-视图合并 （View Merging）
weixin_30670859的博客
04-06 1908
周末时，近日有网友朋友向我提问，“什么是视图合并？多次看到这个名词，但是每次都是扎入到执行计划中去分析了，但是对这个概念和功能完全不清楚，干啥用的？” 对于这个概念，趁赶着这个问题，自己也好好复习一下。一.什么是视图合并(View Merging)？我们通过一个SQL案例来说明，现有如下SQL：SQL1:select emp1.empno, emp1.ename, emp1.commfrom em...
面向 3DoF+的虚拟视图合成算法研究（陈 莹）
weixin_43786637的博客
02-11 866
通过对多视点进行三维空间变换得到虚拟视图处的亚像素信息，并分析这些亚像素与待插值整像素点之间的相关性 依据相关性，不同的应用场景，分别采用三种不同的多视点合成插值算法，分别是最邻近插值、三角插值和非均匀插值。
12.查询转换-视图合并技术
最新发布
weixin_43346403的博客
01-24 465
如果不做视图合并最优:/*+no_merge(view_1)*/视图合并并不总是是最优的执行计划，要视图具体情况来看。如果视图合并最优:/*+merge(view_1)*/2.简单视图合并的例子。
2.7 视图合并
media_test2的博客
02-02 1729
2.7 视图合并正在更新内容，请稍后
视图合并、hash join连接列数据分布不均匀引发的惨案
FaN()
08-26 2460
表大小SQL> select count(*) from agent.TB_AGENT_INFO; COUNT(*) ---------- 1751SQL> select count(*) from TB_CHANNEL_INFO ; COUNT(*) ---------- 1807SQL> select count(*) from TB_USER_CHANNEL; C
Stereo Magnification: 使用多平面图像学习视图合成-论文翻译
基尔霍夫的猫
11-26 1817
简介 过去十年，摄影经历了一场巨变。手机摄像头逐渐取代了傻瓜相机，并且开始与数码单反产生竞争。这一变化是由不断提高的手机相机图像质量所推动的，这不仅仅得益于更好的硬件，还得益于像高动态范围光照和合成散焦这样的图形摄影功能。最近的许多创新都视图复制传统相机的功能。然而，手机上新型传感器的飞速发展，如多镜头和深度传感器，使其应用超越了传统摄影。 特别的是，双摄手机越来越普遍。虽然立体相机的历史和摄影差...
oracle的查询转化之简单视图合并及外连接视图合并
cty48663的博客
12-08 361
参考：https://blogs.oracle.com/optimizer/entry/optimizer_transformations_view_merging_part_1 本文演示所用的表均来自Oracle sampl...
Oracle 12CR2查询转换之视图合并
10-16 276
这里的测试数据库版本为12.2.0.1,在视图合并中，优化器代表视图的查询块到包含视国的查询块中。视图合并通过让优化器考虑额外的连接顺序，访问方法与其它转换来提高性能。例如，在一个视图被合并后并且在一个查询块中有多个表，内置...
hint oracle group by,ORACLE的视图合并HINT
weixin_33914982的博客
04-05 579
第一章ORACLE的视图合并介绍1.1视图合并首先介绍一下ORACLE的视图合并概念：视图合并(View Merging)是优化器处理带视图的目标SQL的一种优化手段，它是指优化器不再将目标SQL中视图的定义语句当做一个独立的处理单元来执行，而是会将其拆开，把其定义SQL语句的基表拿出来与外表合并，合并后的SQL将只剩下外部查询的表和原视图中的基表，不再有视图出现。视图合并的好处是让优化器有更多的...
Laravel 使用视图合成器 view composer 实现视图变量共享
kay_520的专栏
04-02 427
通常我们这样做来实现视图变量全局共享 <?php namespace App\Providers; use Illuminate\Support\Facades\View; class AppServiceProvider extends ServiceProvider { /** * 注册任何应用服务 * * @return void */ public function register() { // }
视图合并和谓词推入
DATA
09-26 2290
------------------------------《海量数据库解决方案》读书笔记-----视图合并（View Merging)------------------------ 一、视图合并和谓词推入 1.视图查询分为两类： 视图查询和读取查询          视图查询：创建视图时所使用的select语句，存储在数据字典中          读取查询：执行该视图（或嵌套视图）的S
JDBC中PreparedStatement常用操作实践
小小默：进无止境
06-12 3984
JDBC使用Statement和PreparedStatement实现批处理功能。 PreparedStatement： 1） addBatch()将一组参数添加到PreparedStatement对象内部。 2） executeBatch()将一批参数提交给数据库来执行，如果全部命令执行成功，则返回更新计数组成的数组。 Statement： 1） addBatch(String sql)方法会在批处理缓存中加入一条sql语句。 2）
数据库的视图中将一行多列和一列多行的两个表合并成
Caiabcd的博客
09-27 361
菜菜再一次起航，还是不废话，至于功能怎么用就看你啦 现在我的数据库中有两个表一个是学生的学号加姓名，一个是学号加各科成绩，两个数据表如下 最后的到的效果图如下 代码如下（注意我们是直接操作的数据库，生成视图。由于本人也是菜鸟，所有就不胡乱给大家科普类，大家自行查阅和体会吧，哈哈） SELECT `学生成绩一`.`学号` AS `学号`, `学生信息`.`姓名` AS `姓名`, max( ( CASE `学生成绩一`.`科目` WHEN '数学' THEN `学生成绩一`.`分数` ELSE 0
创建两张视图的差异视图
06-02
2. 使用UNION ALL运算符将两个视图合并为一个结果集，如下所示： ``` SELECT column1, column2, column3 FROM view1 UNION ALL SELECT column1, column2, column3 FROM view2 ``` 3. 将结果集按照关键字段进行排序...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值