left outer join 改写标量子查询
来源：互联网 发布：linux命令行配置网络 编辑：程序博客网 时间：2024/06/05 04:05
统计每个部门员工总工资。 原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')阅读全文
0 0
- left outer join 改写标量子查询
- SQL改写， 聚合语句， left join 改写标量子查询
- 标量子查询改写
- 标量子查询SQL改写
- 不等值标量子查询改写
- SQL优化-标量子查询的改写
- left outer join 结合条件查询
- Oracle的left outer join查询(转)
- oracle标量子查询简介和表连接改写
- left outer join partition
- LEFT OUTER JOIN
- join联接查询--SQL中 inner join、 left join 、right join、 outer join之间的区别
- left outer join right outer join
- 【Access】 LEFT OUTER JOIN 关联多表的查询语句
- 左外连接查询语句（left outer join）
- left join 或 left outer join
- LEFT JOIN 或 LEFT OUTER JOIN
- left join 和 left outer join
- Delphi TADOQuery Access数据库字符串为空
- OpenGL ES中的变化矩阵
- input前加上label标签
- xx-net在chrome下请检查浏览器代理设置或不是私密连接解决办法
- 干货分享 | 最新机器学习视频教程与数据集下载（持续更新......）
- left outer join 改写标量子查询
- 一个用RecyclerView实现的画廊效果，被选中item居中放大，效果很自然。
- 立体匹配---动态规划
- 数组和链表的区别
- 【Java】实体类为什么要实现序列化
- java 面试题(四)
- Java Socket编程
- liunux 云 服务器防火墙
- EXTJS中如何获得TabPanel的各个tab的点击事件
