标量子查询中exist转化成外连接

最新推荐文章于 2022-10-01 23:20:37 发布
最新推荐文章于 2022-10-01 23:20:37 发布
阅读量1.2k 收藏
点赞数
分类专栏： sql优化 文章标签： oracle sql优化 执行计划 优化
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/gdmzlhj1/article/details/37694001
版权
今天姚工发来个语句，说gz地市的一个语句跑得很慢，1个多钟了还不出结果：


原来语句：

select /*+parallel(t,5)*/
                 case when to_number(to_char(t.open_date,'yyyy'))<2012 then '2012' else to_char(t.open_date,'yyyymm') end as open_date,
          serv_id,divide_market_6,develop_channel_new,is_intelligence,is_fee_user,is_new_user,
                 case when srhx_fee/100 >=0 and srhx_fee/100<5 then 1
                      when srhx_fee/100 >=5 and srhx_fee/100<10 then 2
                      when srhx_fee/100 >=10 and srhx_fee/100<20 then 3
                      when srhx_fee/100 >=20 and srhx_fee/100<30 then 4
                      when srhx_fee/100 >=30 and srhx_fee/100<40 then 5
                      when srhx_fee/100 >=40 and srhx_fee/100<50 then 6
                      when srhx_fee/100 >=50 and srhx_fee/100<60 then 7
                      when srhx_fee/100 >=60 and srhx_fee/100<80 then 8
                      when srhx_fee/100 >=80 and srhx_fee/100<100 then 9
                      when srhx_fee/100 >=100 and srhx_fee/100<150 then 10
                      when srhx_fee/100 >=150 and srhx_fee/100<200 then 11
                      when srhx_fee/100 >=200 and srhx_fee/100<500 then 12
                      when srhx_fee/100 >=500 then 13
                      else -1 end as srhx_fee,
                   dumb_terminal_apply_type,cdma_disc_type,
                case when t1.disc_fee_market >=0 and t1.disc_fee_market<10 then 1
                      when t1.disc_fee_market >=10 and t1.disc_fee_market<19 then 2
                      when t1.disc_fee_market >=19 and t1.disc_fee_market<49 then 3
                      when t1.disc_fee_market >=49 and t1.disc_fee_market<89 then 4
                      when t1.disc_fee_market >=89 and t1.disc_fee_market<189 then 5
                      when t1.disc_fee_market >=189 then 6
                      else -1 end as disc_fee_market,
                is_yx,
                case when SALESTAFF_ID  is null or SALESTAFF_ID = '-1' then 0
                  else 1 end as IS_SALESTAFF,
                case  when exists(select 1  from mdmuser.CCC t1 where t.SALESTAFF_ID=t1.zqlzr_id) then 1
                      else 0 end  as IS_GROUP_SALESTAFF
                 from SUMMARY_SJZ_GZ.AAA t,mdmuser.BBB t1 where t.CDMA_DISC_TYPE=t1.cdma_disc_type3 AND (t.is_fee_user=1 OR IS_NEW_USER=1) 
                 ;


Plan hash value: 3234892192
 
---------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                             |    24M|  4196M| 15218   (3)| 00:04:34 |        |      |            |
|*  1 |  TABLE ACCESS FULL                     | CCC     |   667 |  8004 |     2   (0)| 00:00:01 |        |      |            |
|   2 |  PX COORDINATOR                        |                             |       |       |            |          |        |      |            |
|   3 |   PX SEND QC (RANDOM)                  | :TQ10001                    |    24M|  4196M| 15218   (3)| 00:04:34 |  Q1,01 | P->S | QC (RAND)  |
|*  4 |    HASH JOIN                           |                             |    24M|  4196M| 15218   (3)| 00:04:34 |  Q1,01 | PCWP |            |
|   5 |     BUFFER SORT                        |                             |       |       |            |          |  Q1,01 | PCWC |            |
|   6 |      PX RECEIVE                        |                             |   783 | 20358 |    13   (8)| 00:00:01 |  Q1,01 | PCWP |            |
|   7 |       PX SEND BROADCAST                | :TQ10000                    |   783 | 20358 |    13   (8)| 00:00:01 |        | S->P | BROADCAST  |
|   8 |        VIEW                            | BBB       |   783 | 20358 |    13   (8)| 00:00:01 |        |      |            |
|   9 |         UNION-ALL                      |                             |       |       |            |          |        |      |            |
|  10 |          TABLE ACCESS FULL             | BBB       |   782 |   132K|     6   (0)| 00:00:01 |        |      |            |
|  11 |          HASH UNIQUE                   |                             |     1 |   206 |     7  (15)| 00:00:01 |        |      |            |
|  12 |           NESTED LOOPS ANTI            |                             |    15 |  3090 |     6   (0)| 00:00:01 |        |      |            |
|  13 |            MERGE JOIN CARTESIAN        |                             |    15 |  2985 |     6   (0)| 00:00:01 |        |      |            |
|* 14 |             TABLE ACCESS BY INDEX ROWID| BBB       |     1 |   174 |     2   (0)| 00:00:01 |        |      |            |
|* 15 |              INDEX RANGE SCAN          | IXDIM_CDMA_DISC_TYPE_05     |     1 |       |     1   (0)| 00:00:01 |        |      |            |
|  16 |             BUFFER SORT                |                             |   632 | 15800 |     4   (0)| 00:00:01 |        |      |            |
|* 17 |              TABLE ACCESS FULL         | TB_CFG_SUMCLASSCODE_MAPPING |   632 | 15800 |     4   (0)| 00:00:01 |        |      |            |
|* 18 |            INDEX RANGE SCAN            | IXDIM_CDMA_DISC_TYPE_05     |     1 |     7 |     0   (0)| 00:00:01 |        |      |            |
|  19 |     PX BLOCK ITERATOR                  |                             |  5001K|   744M| 15187   (3)| 00:04:34 |  Q1,01 | PCWC |            |
|* 20 |      TABLE ACCESS FULL                 | AAA      |  5001K|   744M| 15187   (3)| 00:04:34 |  Q1,01 | PCWP |            |
---------------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("T1"."ZQLZR_ID"=:B1)
   4 - access("T"."CDMA_DISC_TYPE"="T1"."CDMA_DISC_TYPE3")
  14 - filter("CDMA_DISC_TYPE3_NAME"='无')
  15 - access("CDMA_DISC_TYPE3"=(-1))
  17 - filter("DES_FIELD"='CDMA_DISC_TYPE')
  18 - access("B"."CDMA_DISC_TYPE3"=TO_NUMBER("A"."CLASS_CODE"))
  20 - filter("T"."CDMA_DISC_TYPE" IS NOT NULL AND ("T"."IS_FEE_USER"=1 OR "IS_NEW_USER"=1))
 
Note
-----
   - dynamic sampling used for this statement

改写后： 

select /*+parallel(t,5)*/
                 case when to_number(to_char(t.open_date,'yyyy'))<2012 then '2012' else to_char(t.open_date,'yyyymm') end as open_date,
          serv_id,divide_market_6,develop_channel_new,is_intelligence,is_fee_user,is_new_user,
                 case when srhx_fee/100 >=0 and srhx_fee/100<5 then 1
                      when srhx_fee/100 >=5 and srhx_fee/100<10 then 2
                      when srhx_fee/100 >=10 and srhx_fee/100<20 then 3
                      when srhx_fee/100 >=20 and srhx_fee/100<30 then 4
                      when srhx_fee/100 >=30 and srhx_fee/100<40 then 5
                      when srhx_fee/100 >=40 and srhx_fee/100<50 then 6
                      when srhx_fee/100 >=50 and srhx_fee/100<60 then 7
                      when srhx_fee/100 >=60 and srhx_fee/100<80 then 8
                      when srhx_fee/100 >=80 and srhx_fee/100<100 then 9
                      when srhx_fee/100 >=100 and srhx_fee/100<150 then 10
                      when srhx_fee/100 >=150 and srhx_fee/100<200 then 11
                      when srhx_fee/100 >=200 and srhx_fee/100<500 then 12
                      when srhx_fee/100 >=500 then 13
                      else -1 end as srhx_fee,
                   dumb_terminal_apply_type,
                   cdma_disc_type,
                case when t1.disc_fee_market >=0 and t1.disc_fee_market<10 then 1
                      when t1.disc_fee_market >=10 and t1.disc_fee_market<19 then 2
                      when t1.disc_fee_market >=19 and t1.disc_fee_market<49 then 3
                      when t1.disc_fee_market >=49 and t1.disc_fee_market<89 then 4
                      when t1.disc_fee_market >=89 and t1.disc_fee_market<189 then 5
                      when t1.disc_fee_market >=189 then 6
                      else -1 end as disc_fee_market,
                is_yx,
                case when SALESTAFF_ID  is null or SALESTAFF_ID = '-1' then 0
                  else 1 end as IS_SALESTAFF,
                case  when t3.zqlzr_id is null then 0  else 1 end  as IS_GROUP_SALESTAFF
                 from
                  SUMMARY_SJZ_gz.AAA t,mdmuser.BBB t1 ,
                  (select distinct zqlzr_id from mdmuser.CCC) t3
                 where t.CDMA_DISC_TYPE=t1.cdma_disc_type3 
                 AND (t.is_fee_user=1 OR IS_NEW_USER=1)
                  and t.SALESTAFF_ID=t3.zqlzr_id(+) ;


Plan hash value: 1662846629
 
-----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                             |    24M|  4472M|       | 15382   (3)| 00:04:37 |        |      |            |
|   1 |  PX COORDINATOR                        |                             |       |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)                  | :TQ10002                    |    24M|  4472M|       | 15382   (3)| 00:04:37 |  Q1,02 | P->S | QC (RAND)  |
|*  3 |    HASH JOIN                           |                             |    24M|  4472M|       | 15382   (3)| 00:04:37 |  Q1,02 | PCWP |            |
|   4 |     BUFFER SORT                        |                             |       |       |       |            |          |  Q1,02 | PCWC |            |
|   5 |      PX RECEIVE                        |                             |   783 | 20358 |       |    13   (8)| 00:00:01 |  Q1,02 | PCWP |            |
|   6 |       PX SEND BROADCAST                | :TQ10000                    |   783 | 20358 |       |    13   (8)| 00:00:01 |        | S->P | BROADCAST  |
|   7 |        VIEW                            | BBB       |   783 | 20358 |       |    13   (8)| 00:00:01 |        |      |            |
|   8 |         UNION-ALL                      |                             |       |       |       |            |          |        |      |            |
|   9 |          TABLE ACCESS FULL             | BBB       |   782 |   132K|       |     6   (0)| 00:00:01 |        |      |            |
|  10 |          HASH UNIQUE                   |                             |     1 |   206 |       |     7  (15)| 00:00:01 |        |      |            |
|  11 |           NESTED LOOPS ANTI            |                             |    15 |  3090 |       |     6   (0)| 00:00:01 |        |      |            |
|  12 |            MERGE JOIN CARTESIAN        |                             |    15 |  2985 |       |     6   (0)| 00:00:01 |        |      |            |
|* 13 |             TABLE ACCESS BY INDEX ROWID| BBB       |     1 |   174 |       |     2   (0)| 00:00:01 |        |      |            |
|* 14 |              INDEX RANGE SCAN          | IXDIM_CDMA_DISC_TYPE_05     |     1 |       |       |     1   (0)| 00:00:01 |        |      |            |
|  15 |             BUFFER SORT                |                             |   632 | 15800 |       |     4   (0)| 00:00:01 |        |      |            |
|* 16 |              TABLE ACCESS FULL         | TB_CFG_SUMCLASSCODE_MAPPING |   632 | 15800 |       |     4   (0)| 00:00:01 |        |      |            |
|* 17 |            INDEX RANGE SCAN            | IXDIM_CDMA_DISC_TYPE_05     |     1 |     7 |       |     0   (0)| 00:00:01 |        |      |            |
|* 18 |     HASH JOIN RIGHT OUTER              |                             |  5001K|   801M|       | 15351   (3)| 00:04:37 |  Q1,02 | PCWP |            |
|  19 |      BUFFER SORT                       |                             |       |       |       |            |          |  Q1,02 | PCWC |            |
|  20 |       PX RECEIVE                       |                             | 66663 |   781K|       |   157   (2)| 00:00:03 |  Q1,02 | PCWP |            |
|  21 |        PX SEND BROADCAST               | :TQ10001                    | 66663 |   781K|       |   157   (2)| 00:00:03 |        | S->P | BROADCAST  |
|  22 |         VIEW                           |                             | 66663 |   781K|       |   157   (2)| 00:00:03 |        |      |            |
|  23 |          HASH UNIQUE                   |                             | 66663 |   781K|  2656K|   157   (2)| 00:00:03 |        |      |            |
|  24 |           TABLE ACCESS FULL            | CCC     | 66663 |   781K|       |    31   (0)| 00:00:01 |        |      |            |
|  25 |      PX BLOCK ITERATOR                 |                             |  5001K|   744M|       | 15190   (3)| 00:04:34 |  Q1,02 | PCWC |            |
|* 26 |       TABLE ACCESS FULL                | AAA      |  5001K|   744M|       | 15190   (3)| 00:04:34 |  Q1,02 | PCWP |            |
-----------------------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("T"."CDMA_DISC_TYPE"="T1"."CDMA_DISC_TYPE3")
  13 - filter("CDMA_DISC_TYPE3_NAME"='无')
  14 - access("CDMA_DISC_TYPE3"=(-1))
  16 - filter("DES_FIELD"='CDMA_DISC_TYPE')
  17 - access("B"."CDMA_DISC_TYPE3"=TO_NUMBER("A"."CLASS_CODE"))
  18 - access("T"."SALESTAFF_ID"="T3"."ZQLZR_ID"(+))
  26 - filter("T"."IS_FEE_USER"=1 OR "IS_NEW_USER"=1)
 
Note
-----
   - dynamic sampling used for this statement



查询结果minus一下为null，说明结果一致

总结：mdmuser.BBB t1 是视图,视图里面有笛卡尔乘积（MERGE JOIN CARTESIAN），一开始以为是这里产生的性能问题，查了视图的结果，返回数量非常少，不存在性能问题。标量子查询中的exists引起了我的注意，这里我改成了左外连接，让它走HASH JOIN RIGHT OUTER，一开始没加distinct，结果出来的记录数跟跑出来的不一样，翻倍增长，原因是mdmuser.CCC表的关联字段zqlzr_id有非常多重复的值。加完distinct之后，问题解决。


优化之后2分钟出结果，其实有些地方还能够进一步优化，但是没必要了，主要的性能瓶颈已经解决，其他都是细枝末节。

gdmzlhj1
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 2
    评论
专栏目录
3. 子查询、事务
Liaily的博客
08-20 499
​事务(Transaction)，一般是指要做的或所做的事情。在计算机术语中是指访问并可能更新数据库中各种数据项的一个程序执行单元(unit)。在计算机术语中，事务通常就是指数据库事务。​ 一个数据库事务通常包含对数据库进行读或写的一个操作序列。它的存在包含有以下两个目的：1、为数据库操作提供了一个从失败中恢复到正常状态的方法，同时提供了数据库即使在异常状态下仍能保持一致性的方法。2、当多个应用程序在并发访问数据库时，可以在这些应用程序之间提供一个隔离方法，以防止彼此的操作互相干扰。
三、复杂查询方法-视图、子查询、函数等
mangyankao2396的博客
03-07 279
目录1. 视图1.1 创建视图 1. 视图 视图是一个虚拟的表，不同于数据表，视图是通过select语句进行创建的，视图和数据表的区别就是“是否保存了实际的数据”，视图没有存储真实的数据，而是一个窗口，通过视图可以看到数据表中真是存在的数据。 1.1 创建视图 单表视图 create view view_name(col_name_1,...,col_name_n) as select 语句 【例子】创建视图stu_sum，统计每个年龄段的人数 create view stu_sum(age,age
exists改为内连接
csdn11223的博客
10-29 419
SQL> select dname from dept 2 where exists(select * from emp where deptno=dept.deptno) 3 /DNAME------------...
外连接,exists存在,(DML):用SQL录入数据，用SQL删除数据，用SQL更新数据
qq_45939736的博客
08-05 192
外连接, exists存在, (DML):用SQL录入数据，用SQL删除数据，用SQL更新数据
12c 中的标量子查询被transform成外连接
cmbpr28628的专栏
08-22 106
select (select max(object_id) from test2 b where b.object_id =a.object_id) from test1 a 变成如下： 点击(此...
标量子查询优化外连接
莫等闲
09-25 2016
同事发来2个语句，说语句1跑得慢，语句2很快就出结果。一执行，果然很慢。仔细发现，2个语句不等价。 语句1： select l.*,o.object_name from v$locked_object l left join all_objects o on l.object_id=o.object_id; 语句2： select l.*,o.object_name from v$
mysql标量子查询分析优化
kissandlove的博客
02-21 641
在做结算的业务组件是发现其他开发有在for循环中做select查询。 private List&lt;TSettleCycleResponse&gt; buildCycleListToResponseListForSearch(List&lt;TSettleCycle&gt; cycleList){ List&lt;TSettleCycleResponse&gt; responseLi...
庖丁解牛｜图解 MySQL 8.0 优化器查询转换篇
阿里云云栖号
10-19 1039
简介：本篇介绍子查询、分析表和JOIN的复杂转换过程 一 背景和架构 在《庖丁解牛-图解MySQL 8.0优化器查询解析篇》一文中我们重点介绍了MySQL最新版本8.0.25关于SQL基本元素表、列、函数、聚合、分组、排序等元素的解析、设置和转换过程，本篇我们继续来介绍更为复杂的子查询、分区表和JOIN的复杂转换过程，大纲如下： Transformation remove_redundant_subquery_clause :Permanently remove redundant parts ..
Task03：复杂查询方法-视图、子查询、函数等
weixin_42758462的博客
07-13 148
1.在product表和shop_product表的基础上创建视图 CREATE VIEW view_shop_product(product_type, sale_price, shop_name) AS SELECT product_type, sale_price, shop_name FROM product, shop_product WHERE product.product_id = shop_product.product_id; ...
高级查询、内外连接
m0_55460936的博客
10-01 165
高级查询、内外连接
oracle 外连接改写_第61篇必须通过改写SQL才能提升性能的一些情况
weixin_34979632的博客
01-19 225
这篇文章介绍了一些需要通过改写才能提高性能的SQL写法，也是对本人以前公众号改写相关文章的一个总结(也有新内容)。同时也对网络上流传的一些不太准确的说法给予纠正。改写的首要任务是等价，其次才是性能的提高，不等价的改写危害更大。希望能对大家有一些帮助。一家之言，欢迎留言讨论。1 大结果集标量子查询改外关联 这个改写网上介绍的挺多，改写的两个关键点就是：1、主...
mysql标量子查询变成左连接_标量子查询优化外连接
weixin_34732336的博客
02-02 239
同事发来2个语句，说语句1跑得慢，语句2很快就出结果。一执行，果然很慢。仔细发现，2个语句不等价。 语句1： select l.*,o.object_name from v$locked_object l left join all_objects o on l.object_id=o.object_id;语句2： select l.*,o.object_name from同事发来2个语句，说语句...
mysql连接查询的改写_oracle标量子查询简介和表连接改写
weixin_39622710的博客
02-19 94
之前小鱼就听过了标量子查询，不过对于其中的细节理解还是远远不够，借助一部分资料和自己测试对标量子查询做一点简单的分析和介绍。 Oracle允许在select子句中包含单行子查询，这个也就是oracle的标量子查询，标量子查询有点类似于外连接，当使用到外连接时之前小鱼就听过了标量子查询，不过对于其中的细节理解还是远远不够，借助一部分资料和自己测试对标量子查询做一点简单的分析和介绍。Oracle允许在...
mysql 左连接，右连接，内链接，exists等
cml2016的博客
12-17 618
背景 mysql> select * from grade; +----+-------+ | no | grade | ...
改写优化SQL（4）：行转列标量子查询改为left join
SQL Server探索
01-24 1519
前面两篇文章写的是，如何把 标量子查询、聚合标量子查询，转为 left join，标量子查询还可以实现行转列，但是同样的，销量比较差，建议转成 left join。 （1）建表 --学生表 CREATE TABLE student ( s# INT , sname NVARCHAR(32) , sage INT , ssex NVAR
如何把exists 改为 left join
【设计改变世界】github地址：https://github.com/guochunyang2004
08-14 891
exists方法： SELECT top 10 * FROM t WHERE (PlanName LIKE '%关键字%' OR remark LIKE '%关键字%' OR exists(SELECT pp.tpID FROM pp JOIN points (NOLOCK) p ON p.id = pp.pointID where p.PointNam...
使用表连接替代exists
cmff98425的博客
01-13 415
一般来讲使用表连接，要比使用EXISTS更高效。（我不这样认为，从执行计划来看，EXISTS效率更好） 点击(此处)折叠或打开 --查询sales部门...
将not exists更改为外连接
weixin_33910137的博客
05-27 152
主机CPU一直100%，其中有一条SQL，每秒同时有15进程正在执行，并且性能还不好，要想降低CPU，就得先把这条SQL搞定，估计搞定这条SQL，CPU大概可以降到70%以下。 欢迎大家加入ORACLE超级群：17115662 免费解决各种ORACLE问题,以后BLOG将迁移到http://www.htz.pw ...
（MySql数据库使用经验）MySql的高级查询之内连接,外连接,交叉连接以及on,exists,order by,union关键字
qq_40001362的博客
08-24 1300
连接查询: 将两张表或者两张以上的表进行记录的连接(按照某个指定的条件进行数据拼接)。 连接查询的使用: 在用户查看数据的时候,需要显示的数据来自多张表. 连接查询语法: 需要使用join 关键字。 使用方式为: 左表 join 右表，左表: 在join关键字左边的表，右表: 在join关键字右边的表。 连接查询： SQL中将连接查询分成四类: 分别为，1内连接,2外连接,4交叉连接 ...
数据库查询 not exist 子句
最新发布
10-09
not exist 子句是一种用于查询数据库中不存在某些条件的记录的方法。它可以与 SELECT 语句一起使用，用于筛选出在另一个表中不存在的记录。 not exist 子句的语法如下： ```sql SELECT 列名 FROM 表名 WHERE NOT ...
评论 2
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值