left outer join 改写标量子查询

最新推荐文章于 2022-08-19 17:21:14 发布
最新推荐文章于 2022-08-19 17:21:14 发布
阅读量632 收藏
点赞数
分类专栏： SQL Tuning
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Angelina7/article/details/72623039
版权 
统计每个部门员工总工资。
 原SQL：
select d.department_id,
       d.department_name,
       nvl((select sum(e.salary)
             from employees e
            where e.department_id = d.department_id),
           0) sum_amt
  from departments d;
 PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
Plan hash value: 1383367578

--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |    27 |   432 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE              |                   |     1 |     7 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES         |    10 |    70 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | EMP_DEPARTMENT_IX |    10 |       |     1   (0)| 00:00:01 |
|   4 |  TABLE ACCESS FULL           | DEPARTMENTS       |    27 |   432 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

   3 - access("E"."DEPARTMENT_ID"=:B1)
改写SQL 将子查询结果作为内联视图，与外部表进行关联

 
  select d.department_id,
       d.department_name,
	   sum_amt from departments d left outer join (
  select department_id,sum(e.salary) sum_amt
             from employees e
         group by department_id)e on(d.department_id=e.department_id);
		 PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
Plan hash value: 3662601325

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |    27 |  1134 |     7  (29)| 00:00:01 |
|   1 |  MERGE JOIN OUTER            |             |    27 |  1134 |     7  (29)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |    27 |   432 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | DEPT_ID_PK  |    27 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |             |    11 |   286 |     5  (40)| 00:00:01 |
|   5 |    VIEW                      |             |    11 |   286 |     4  (25)| 00:00:01 |
|   6 |     HASH GROUP BY            |             |    11 |    77 |     4  (25)| 00:00:01 |
|   7 |      TABLE ACCESS FULL       | EMPLOYEES   |   107 |   749 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

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

   4 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID"(+))
       filter("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID"(+))

已选择20行。

根据以上方式改写以下SQL：
  select first_name,
            department_name,
            nvl((select min_salary
                  from jobs
                 where employees.job_id = jobs.job_id
                   and jobs.job_title = 'President'),
                0) pre_min_sal,
            nvl((select min_salary
                  from jobs
                where employees.job_id = jobs.job_id
                  and jobs.job_title = 'Administration Vice President'),
               0) avp_sal,
           nvl((select min_salary
                 from jobs
                where employees.job_id = jobs.job_id
                  and jobs.job_title = 'Administration Assistant'),
               0) aa_sal,
           nvl((select max_salary
                 from jobs
                where employees.job_id = jobs.job_id
                  and jobs.job_title = 'Finance Manager'),
               0) fm_sal,
           nvl((select max_salary
                 from jobs
                where employees.job_id = jobs.job_id
                  and jobs.job_title = 'Public Accountant'),
               0) pa_sal,
           nvl((select max_salary
                 from jobs
                where employees.job_id = jobs.job_id
                  and jobs.job_title = 'Purchasing Manager'),
               0) pm_sal,
           nvl((select city
                 from locations
                where locations.location_id = departments.location_id),
               0) city
      from employees
     inner join departments
        on (employees.department_id = departments.department_id);
		
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------
Plan hash value: 1446905011

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |   106 |  4028 |     6  (17)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID | JOBS        |     1 |    31 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN          | JOB_ID_PK   |     1 |       |     0   (0)| 00:00:01 |
|*  3 |  TABLE ACCESS BY INDEX ROWID | JOBS        |     1 |    31 |     1   (0)| 00:00:01 |
|*  4 |   INDEX UNIQUE SCAN          | JOB_ID_PK   |     1 |       |     0   (0)| 00:00:01 |
|*  5 |  TABLE ACCESS BY INDEX ROWID | JOBS        |     1 |    31 |     1   (0)| 00:00:01 |
|*  6 |   INDEX UNIQUE SCAN          | JOB_ID_PK   |     1 |       |     0   (0)| 00:00:01 |
|*  7 |  TABLE ACCESS BY INDEX ROWID | JOBS        |     1 |    31 |     1   (0)| 00:00:01 |
|*  8 |   INDEX UNIQUE SCAN          | JOB_ID_PK   |     1 |       |     0   (0)| 00:00:01 |
|*  9 |  TABLE ACCESS BY INDEX ROWID | JOBS        |     1 |    31 |     1   (0)| 00:00:01 |
|* 10 |   INDEX UNIQUE SCAN          | JOB_ID_PK   |     1 |       |     0   (0)| 00:00:01 |
|* 11 |  TABLE ACCESS BY INDEX ROWID | JOBS        |     1 |    31 |     1   (0)| 00:00:01 |
|* 12 |   INDEX UNIQUE SCAN          | JOB_ID_PK   |     1 |       |     0   (0)| 00:00:01 |
|  13 |  TABLE ACCESS BY INDEX ROWID | LOCATIONS   |     1 |    12 |     1   (0)| 00:00:01 |
|* 14 |   INDEX UNIQUE SCAN          | LOC_ID_PK   |     1 |       |     0   (0)| 00:00:01 |
|  15 |  MERGE JOIN                  |             |   106 |  4028 |     6  (17)| 00:00:01 |
|  16 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |    27 |   513 |     2   (0)| 00:00:01 |
|  17 |    INDEX FULL SCAN           | DEPT_ID_PK  |    27 |       |     1   (0)| 00:00:01 |
|* 18 |   SORT JOIN                  |             |   107 |  2033 |     4  (25)| 00:00:01 |
|  19 |    TABLE ACCESS FULL         | EMPLOYEES   |   107 |  2033 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

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

   1 - filter("JOBS"."JOB_TITLE"='President')
   2 - access("JOBS"."JOB_ID"=:B1)
   3 - filter("JOBS"."JOB_TITLE"='Administration Vice President')
   4 - access("JOBS"."JOB_ID"=:B1)
   5 - filter("JOBS"."JOB_TITLE"='Administration Assistant')
   6 - access("JOBS"."JOB_ID"=:B1)
   7 - filter("JOBS"."JOB_TITLE"='Finance Manager')
   8 - access("JOBS"."JOB_ID"=:B1)
   9 - filter("JOBS"."JOB_TITLE"='Public Accountant')
  10 - access("JOBS"."JOB_ID"=:B1)
  11 - filter("JOBS"."JOB_TITLE"='Purchasing Manager')
  12 - access("JOBS"."JOB_ID"=:B1)
  14 - access("LOCATIONS"."LOCATION_ID"=:B1)
  18 - access("EMPLOYEES"."DEPARTMENT_ID"="DEPARTMENTS"."DEPARTMENT_ID")
       filter("EMPLOYEES"."DEPARTMENT_ID"="DEPARTMENTS"."DEPARTMENT_ID")

已选择45行。
修改SQL:
select e.first_name,
       d.department_name,
       nvl(j.pre_min_sal, 0) pre_min_sal,
       nvl(j.avp_sal, 0) avp_sal,
       nvl(j.aa_sal, 0) aa_sal,
       nvl(j.fm_sal, 0) fm_sal,
       nvl(j.pa_sal, 0) pa_sal,
       nvl(j.pm_sal, 0) pm_sal,
       nvl(l.city, 0) city
  from employees e
  left outer join (select j1.job_id,
                          decode(job_title, 'President', min_salary) pre_min_sal,
                          decode(job_title,
                                 'Administration Vice President',
                                 min_salary) avp_sal,
                          decode(job_title,
                                 'Administration Assistant',
                                 min_salary) aa_sal,
                          decode(job_title, 'Finance Manager', max_salary) fm_sal,
                          decode(job_title, 'Public Accountant', max_salary) pa_sal,
                          decode(job_title, 'Purchasing Manager', max_salary) pm_sal
                     from jobs j1
                    where job_title in ('President',
                                        'Administration Vice President',
                                        'Administration Assistant',
                                        'Finance Manager',
                                        'Public Accountant',
                                        'Purchasing Manager')) j
    on (j.job_id = e.job_id)
 inner join departments d
    on (e.department_id = d.department_id)
  left outer join locations l
    on (d.location_id = l.location_id);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------
Plan hash value: 1485523108

---------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                  |   106 |  8798 |    13  (24)| 00:00:01 |
|*  1 |  HASH JOIN RIGHT OUTER         |                  |   106 |  8798 |    13  (24)| 00:00:01 |
|   2 |   VIEW                         | index$_join$_007 |    23 |   276 |     3  (34)| 00:00:01 |
|*  3 |    HASH JOIN                   |                  |       |       |            |          |
|   4 |     INDEX FAST FULL SCAN       | LOC_CITY_IX      |    23 |   276 |     1   (0)| 00:00:01 |
|   5 |     INDEX FAST FULL SCAN       | LOC_ID_PK        |    23 |   276 |     1   (0)| 00:00:01 |
|*  6 |   HASH JOIN OUTER              |                  |   106 |  7526 |    10  (20)| 00:00:01 |
|   7 |    MERGE JOIN                  |                  |   106 |  4028 |     6  (17)| 00:00:01 |
|   8 |     TABLE ACCESS BY INDEX ROWID| DEPARTMENTS      |    27 |   513 |     2   (0)| 00:00:01 |
|   9 |      INDEX FULL SCAN           | DEPT_ID_PK       |    27 |       |     1   (0)| 00:00:01 |
|* 10 |     SORT JOIN                  |                  |   107 |  2033 |     4  (25)| 00:00:01 |
|  11 |      TABLE ACCESS FULL         | EMPLOYEES        |   107 |  2033 |     3   (0)| 00:00:01 |
|* 12 |    TABLE ACCESS FULL           | JOBS             |     6 |   198 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

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

   1 - access("D"."LOCATION_ID"="L"."LOCATION_ID"(+))
   3 - access(ROWID=ROWID)
   6 - access("J1"."JOB_ID"(+)="E"."JOB_ID")
  10 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
       filter("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
  12 - filter("JOB_TITLE"(+)='Administration Assistant' OR "JOB_TITLE"(+)='Administration
              Vice President' OR "JOB_TITLE"(+)='Finance Manager' OR "JOB_TITLE"(+)='President' OR
              "JOB_TITLE"(+)='Public Accountant' OR "JOB_TITLE"(+)='Purchasing Manager')


Teresa7
关注
专栏目录
标量子查询优化（用group by 代替distinct）
zhaoyangjian724的专栏
12-06 1550
标量子查询优化 当使用另外一个SELECT 语句来产生结果中的一列的值的时候,这个查询必须只能返回一行一列的值。这种类型的子查询被称为标量子查询 在某些情况下可以进行优化以减少标量子查询的重复执行,但更糟糕的场景是每一行都需要标量子查询的执行。 explain plan for SELECT B.EMP_NO, B.CUST_NO, B.CU
PLSQL 子查询(WITH)，表链接 LEFT RIGTH FULL
Tapyou的博客
10-25 1529
--多列 子查询当表使用 --查询各部门工资最高的前三名 select * from (select ename,sal,deptno,rank()over(partition by deptno order by sal desc) 排名 from emp) where 排名<=3 --查询emp表每个部门的最高工资,最低工资,人数,平均工资 --显示(最高工资-最低工资)/人数>平均工资的三分之一的部门 select deptno,max(sal),min(sal),c...
mysql标量子查询变成左连接_MYSQL count标量子查询改left join
weixin_35638013的博客
02-02 256
SELECT homepageId, userId, homepagesummary, totalviews, totalleadpercents, totalappointments, homepagestatus, linkphone, imagepath, createtime, updatetime, recommendtime, sortcode, desig...
mysql标量子查询变成左连接_标量子查询优化外连接
weixin_34732336的博客
02-02 240
同事发来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基础：子查询、内部联结inner join、左外联结left outer join、右外联结right outer join、合并查询union与union all、全文本搜索
lingaixuexi的博客
01-12 445
第14章 子查询 #列出订购物品TNT2的所有客户的姓名、联系人 1、在orderitems表中由prod_id查找order_num select order_num from  orderitems where prod_id='TNT2' 2、在orders表中由order_num查找cust_id select cust_id from orders where order_num in ...
mysql标量子查询变成左连接,在MySQL中使用子查询和标量子查询的基本操作教程...
weixin_31476341的博客
03-18 172
MySQL 子查询子查询是将一个 SELECT 语句的查询结果作为中间结果，供另一个 SQL 语句调用。MySQL 支持 SQL 标准要求的所有子查询格式和操作，也扩展了特有的几种特性。子查询没有固定的语法，一个子查询的例子如下：?对应的两个数据表如下：article 文章表:user 用户表:查询返回结果如下所示：在该例子中，首先通过子查询语句查询出所有 status=1 的 uid，实际的查询...
oracle标量子查询的优势,oracle标量子查询简介和表连接改写
weixin_42528780的博客
04-04 447
oracle标量子查询简介和表连接改写之前小鱼就听过了标量子查询，不过对于其中的细节理解还是远远不够，借助一部分资料和自己测试对标量子查询做一点简单的分析和介绍。Oracle允许在select子句中包含单行子查询，这个也就是oracle的标量子查询，标量子查询有点类似于外连接，当使用到外连接时我们可以灵活的将其转化为标量子查询。SQL> create table t1 as select *...
罗炳森-SQL等价改写核心思想
04-27
此外，标量子查询（Scalar Subquery）在SQL等价改写中也有重要作用，它可以用于表示单个值的查询。在某些情况下，将标量子查询嵌入到主查询中可以优化查询性能，但这通常需要谨慎处理，因为过度使用或不恰当的使用...
数据库查询优化器，RBO优化规则介绍及示例
Aiky哇
08-19 1849
数据库查询优化器是针对于sql经过解析后生成的ast表达式树的。目的是能够降低sql执行计算量，简化计算。传统数据库中，查询优化是很复杂的，大体上可以分为RBO和CBO，其中CBO的收益性不确定，需要进行代价估算，依赖的数据统计会比较多。而RBO规则优化在不需要了解数据统计信息的前提下，可以明确提升sql执行计划的查询性能。现在的数据库厂商大多使用的是RBO+CBO或者只用CBO的架构。其实只用CBO更主流一些，TIDB,PolarX都在用，只用cbo搜索空间相对更完整，优化结果更接近全局最优。
SQL 子查询怎么优化？写的很深的这种
q1472750149的博客
11-18 1191
子查询（Subquery）的优化一直以来都是 SQL 查询优化中的难点之一。关联子查询的基本执行方式类似于 Nested-Loop，但是这种执行方式的效率常常低到难以忍受。当数据量稍大时，必须在优化器中对其进行去关联化（Decoorelation 或 Unnesting），将其改写为类似于 Semi-Join 这样的更高效的算子。 前人已经总结出一套完整的方法论，理论上能对任意一个查询进行去关联化。本文结合 SQL Server 以及 HyPer 的几篇经典论文，由浅入深地讲解一下这套去关联化的理论体..
关于外联接(Outer Join)及其他
ciwan8376的博客
03-24 215
一 使用外联接以前在Oracle中用=(+)和(+)=来进行左外联接和右外联接；后来用SQL Server时用*=和=*进行外连接左外联接和右外联接；现在决定用SQL-92的标准方法：[OUTER] JOINOUTER是可以省略...
SQL优化-标量子查询的改写
killvoon的专栏
05-25 3273
原语句： with d as (select * from dc.Dcam_Lbsq d where d.inptdt between to_date(substr('20110930', 1, 6) || '01', 'yyyymmdd') and to_date('20110930', 'yyyymmdd') and d.lnbl
标量子查询优化测试
09-04 160
简介 今天主要是研究了下对sql中对标量子查询的优化思路，觉得有些收获，特别把研究的过程发出来供爱好者们一同学习研究。 1、搭建测试环境 --db version：oracle 11g R2 单机环...
改写优化SQL（4）：行转列标量子查询改为left join
SQL Server探索
01-24 1520
前面两篇文章写的是，如何把 标量子查询、聚合标量子查询，转为 left join，标量子查询还可以实现行转列，但是同样的，销量比较差，建议转成 left join。 （1）建表 --学生表 CREATE TABLE student ( s# INT , sname NVARCHAR(32) , sage INT , ssex NVAR
[Hive]子查询使用指南
开嘴的板栗
03-23 7315
1、在from语句中使用子查询 Hive在0.12版本后就支持了from条件中子查询，例如： SELECT ... FROM (subquery) name ... SELECT ... FROM (subquery) AS name ... (Note: Only valid starting with Hive 0.13.0) 但是在from语句中使用子查询，必须给子查询一个名称，因为 FROM 子句中的每个表都必须有一个名称。 子查询选择列表中的列必须具有唯一的名称。子查询选择列表中的列在外部查
left join 改写标量子查询
weixin_30646505的博客
09-03 429
数据库环境：SQL SERVER 2005 　　有一博彩的赔率是1:20，它有2张业务表：smuchs（投注表），lottery（开奖表）。 smuchs表有3个字段，分别是sno（投注号码）、smuch（投注金额），stime（投注时间）， lottery表有2个字段，分别是lno（开奖号码）、stime（开奖时间）。smuchs表和lottery表的数据如下： 　　要求...
SQL子查询的优化
renq的专栏
01-06 836
本文作者： Eric Fu 本文链接： https://ericfu.me/subquery-optimization/ 版权声明： 本博客所有文章除特别声明外，均采用 BY-NC-SA 许可协议。转载请注明出处！ 子查询（Subquery）的优化一直以来都是 SQL 查询优化中的难点之一。关联子查询的基本执行方式类似于 Nested-Loop，但是这种执行方式的效率常常低到难以忍受。当数据量...
left outer join和left join区别
最新发布
03-15
使用LEFT JOIN/LEFT OUTER JOIN查询Customers表和Orders表的结果如下： SELECT Customers.Name, Orders.OrderDate FROM Customers LEFT JOIN Orders ON Customers.ID = Orders.CustomerID; 结果： +--+ | John | ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值