标量子查询SQL改写
来源：互联网 发布：php 判断是整数 编辑：程序博客网 时间：2024/05/29 08:11
一网友说下面sql跑的好慢，让我看看
sql代码：
select er, cid, pid, tbl, zs, sy, (select count(sr.mobile_tele_no) from tbl_sp_sales_records sr where sr.task_id = tid and sr.channel_id = cid and sr.is_conn = '1' and sr.sales_time >='2017-10-01 00:00:00' and sr.sales_time <='2017-10-27 00:00:00' ) hc1, (select count(sr.mobile_tele_no) from tbl_sp_sales_records sr where sr.task_id = tid and sr.channel_id = cid and sr.is_conn = '0' and sr.sales_time >='2017-10-01 00:00:00' and sr.sales_time <='2017-10-27 00:00:00' ) hc2, (select count(1) from tbl_disturb_customer_records cr where cr.target_name = tbl and cr.disturb_type in ('98', '99')) gz, (select count(1) from tbl_disturb_customer_records cr where cr.target_name = tbl and cr.disturb_type not in ('98', '99')) mr from (select c.creator er, tt.target_data tbl, t.channel_id cid, c.create_time ctime, t.task_id tid, c.campaign_id pid, count_table_num_by_channelid(tt.target_data, t.channel_id) zs, count_table_num(tt.target_data) sy from tbl_sp_campaign c, tbl_sp_task t, tbl_task_targetdata tt where c.campaign_id = t.campaign_id and t.task_id = tt.task_id and c.creator in ('fuzhou', 'lingde', 'longyan', 'nanping', 'putian', 'quanzhou', 'sanming', 'xiamen', 'zhangzhou') and c.create_time >= '2017-10-01 00:00:00' and c.create_time <= '2017-10-27 00:00:00')执行计划
PLAN_TABLE_OUTPUTPlan hash value: 2087309529--------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 5 | 670 | 14 (8)| 00:00:01 || 1 | SORT AGGREGATE | | 1 | 74 | | ||* 2 | TABLE ACCESS BY INDEX ROWID | TBL_SP_SALES_RECORDS | 1 | 74 | 9 (0)| 00:00:01 ||* 3 | INDEX RANGE SCAN | IDX_SSR_STAREA | 7 | | 4 (0)| 00:00:01 || 4 | SORT AGGREGATE | | 1 | 74 | | ||* 5 | TABLE ACCESS BY INDEX ROWID | TBL_SP_SALES_RECORDS | 1 | 74 | 9 (0)| 00:00:01 ||* 6 | INDEX RANGE SCAN | IDX_SSR_STAREA | 7 | | 4 (0)| 00:00:01 || 7 | SORT AGGREGATE | | 1 | 26 | | ||* 8 | TABLE ACCESS BY INDEX ROWID | TBL_DISTURB_CUSTOMER_RECORDS | 289 | 7514 | 82 (0)| 00:00:01 ||* 9 | INDEX RANGE SCAN | IDX_TARGET_NAME | 2993 | | 20 (0)| 00:00:01 || 10 | SORT AGGREGATE | | 1 | 26 | | ||* 11 | TABLE ACCESS BY INDEX ROWID | TBL_DISTURB_CUSTOMER_RECORDS | 4058 | 103K| 82 (0)| 00:00:01 ||* 12 | INDEX RANGE SCAN | IDX_TARGET_NAME | 2993 | | 20 (0)| 00:00:01 ||* 13 | HASH JOIN | | 5 | 670 | 14 (8)| 00:00:01 ||* 14 | HASH JOIN | | 5 | 450 | 11 (10)| 00:00:01 ||* 15 | TABLE ACCESS BY INDEX ROWID| TBL_SP_CAMPAIGN | 5 | 225 | 7 (0)| 00:00:01 ||* 16 | INDEX RANGE SCAN | IDX_P_CREATE_TIME | 6 | | 2 (0)| 00:00:01 || 17 | TABLE ACCESS FULL | TBL_SP_TASK | 112 | 5040 | 3 (0)| 00:00:01 || 18 | TABLE ACCESS FULL | TBL_TASK_TARGETDATA | 112 | 4928 | 3 (0)| 00:00:01 |--------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - filter("SR"."TASK_ID"=:B1 AND "SR"."CHANNEL_ID"=:B2 AND "SR"."IS_CONN"='1') 3 - access("SR"."SALES_TIME">='2017-10-01 00:00:00' AND "SR"."SALES_TIME"<='2017-10-27 00:00:00') filter(SUBSTR("SALES_TIME",1,10)>='2017-10-01' AND SUBSTR("SALES_TIME",1,10)<='2017-10-27') 5 - filter("SR"."TASK_ID"=:B1 AND "SR"."CHANNEL_ID"=:B2 AND "SR"."IS_CONN"='0') 6 - access("SR"."SALES_TIME">='2017-10-01 00:00:00' AND "SR"."SALES_TIME"<='2017-10-27 00:00:00') filter(SUBSTR("SALES_TIME",1,10)>='2017-10-01' AND SUBSTR("SALES_TIME",1,10)<='2017-10-27') 8 - filter("CR"."DISTURB_TYPE"='98' OR "CR"."DISTURB_TYPE"='99') 9 - access("CR"."TARGET_NAME"=:B1) 11 - filter("CR"."DISTURB_TYPE"<>'98' AND "CR"."DISTURB_TYPE"<>'99') 12 - access("CR"."TARGET_NAME"=:B1) 13 - access("T"."TASK_ID"="TT"."TASK_ID") 14 - access("C"."CAMPAIGN_ID"="T"."CAMPAIGN_ID") 15 - filter("C"."CREATOR"='fuzhou' OR "C"."CREATOR"='lingde' OR "C"."CREATOR"='longyan' OR "C"."CREATOR"='nanping' OR "C"."CREATOR"='putian' OR "C"."CREATOR"='quanzhou' OR "C"."CREATOR"='sanming' OR "C"."CREATOR"='xiamen' OR "C"."CREATOR"='zhangzhou') 16 - access("C"."CREATE_TIME">='2017-10-01 00:00:00' AND "C"."CREATE_TIME"<='2017-10-27 00:00:00')分析
我跟网友说：让他去掉sql里的标量，运行一次，他说很快
性能瓶颈在于标量子查询上，大家都知道，标量子查询可以改写成left join
改写后代码
select er, cid, pid, tbl, zs, sy, p. hc1, p. hc2, p2.gz, p2. mr from (select c.creator er, tt.target_data tbl, t.channel_id cid, c.create_time ctime, t.task_id tid, c.campaign_id pid, count_table_num_by_channelid(tt.target_data, t.channel_id) zs, count_table_num(tt.target_data) sy from tbl_sp_campaign c, tbl_sp_task t, tbl_task_targetdata tt where c.campaign_id = t.campaign_id and t.task_id = tt.task_id and c.creator in ('fuzhou', 'lingde', 'longyan', 'nanping', 'putian', 'quanzhou', 'sanming', 'xiamen', 'zhangzhou') and c.create_time >= '2017-10-01 00:00:00' and c.create_time <= '2017-10-27 00:00:00') c left join (select sr.task_id, sr.channel_id, count(decode(sr.is_conn,1,sr.mobile_tele_no)) hc1, count(decode(sr.is_conn,0,sr.mobile_tele_no)) hc2, from tbl_sp_sales_records sr where sr.sales_time >='2017-10-01 00:00:00' and sr.sales_time <='2017-10-27 00:00:00' group by sr.task_id,sr.channel_id)p on (p.task_id = c.tid and p.channel_id = c.cid) left join (select count(case when disturb_type in ('98', '99') then 1 end )gz, count(case when disturb_type not in ('98', '99') then 1 end )mr, target_name from tbl_disturb_customer_records group by target_name) p2 on (p2.target_name = c.tbl)执行计划
PLAN_TABLE_OUTPUTPlan hash value: 4214787203-----------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-----------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 5 | 1160 | 435 (1)| 00:00:06 || 1 | NESTED LOOPS OUTER | | 5 | 1160 | 435 (1)| 00:00:06 ||* 2 | HASH JOIN | | 5 | 1020 | 25 (12)| 00:00:01 ||* 3 | HASH JOIN OUTER | | 5 | 800 | 21 (10)| 00:00:01 ||* 4 | HASH JOIN | | 5 | 450 | 11 (10)| 00:00:01 ||* 5 | TABLE ACCESS BY INDEX ROWID | TBL_SP_CAMPAIGN | 5 | 225 | 7 (0)| 00:00:01 ||* 6 | INDEX RANGE SCAN | IDX_P_CREATE_TIME | 6 | | 2 (0)| 00:00:01 || 7 | TABLE ACCESS FULL | TBL_SP_TASK | 112 | 5040 | 3 (0)| 00:00:01 || 8 | VIEW | | 7 | 490 | 10 (10)| 00:00:01 || 9 | HASH GROUP BY | | 7 | 518 | 10 (10)| 00:00:01 || 10 | TABLE ACCESS BY INDEX ROWID| TBL_SP_SALES_RECORDS | 7 | 518 | 9 (0)| 00:00:01 ||* 11 | INDEX RANGE SCAN | IDX_SSR_STAREA | 7 | | 4 (0)| 00:00:01 || 12 | TABLE ACCESS FULL | TBL_TASK_TARGETDATA | 112 | 4928 | 3 (0)| 00:00:01 || 13 | VIEW PUSHED PREDICATE | | 1 | 28 | 82 (0)| 00:00:01 || 14 | SORT GROUP BY | | 1 | 26 | 82 (0)| 00:00:01 || 15 | TABLE ACCESS BY INDEX ROWID | TBL_DISTURB_CUSTOMER_RECORDS | 4342 | 110K| 82 (0)| 00:00:01 ||* 16 | INDEX RANGE SCAN | IDX_TARGET_NAME | 2993 | | 20 (0)| 00:00:01 |-----------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - access("T"."TASK_ID"="TT"."TASK_ID") 3 - access("P"."CHANNEL_ID"(+)="T"."CHANNEL_ID" AND "P"."TASK_ID"(+)="T"."TASK_ID") 4 - access("C"."CAMPAIGN_ID"="T"."CAMPAIGN_ID") 5 - filter("C"."CREATOR"='fuzhou' OR "C"."CREATOR"='lingde' OR "C"."CREATOR"='longyan' OR "C"."CREATOR"='nanping' OR "C"."CREATOR"='putian' OR "C"."CREATOR"='quanzhou' OR "C"."CREATOR"='sanming' OR "C"."CREATOR"='xiamen' OR "C"."CREATOR"='zhangzhou') 6 - access("C"."CREATE_TIME">='2017-10-01 00:00:00' AND "C"."CREATE_TIME"<='2017-10-27 00:00:00') 11 - access("SR"."SALES_TIME">='2017-10-01 00:00:00' AND "SR"."SALES_TIME"<='2017-10-27 00:00:00') filter(SUBSTR("SALES_TIME",1,10)>='2017-10-01' AND SUBSTR("SALES_TIME",1,10)<='2017-10-27') 16 - access("TARGET_NAME"="TT"."TARGET_DATA")如果大家有兴趣，可以拿着以下sql代码进行测试。
改写前的：
select d.department_id, d.department_name, d.location_id, NVL((select SUM(e.salary) from employees e where e.department_id = d.department_id and e.job_id = 'IT_PROG'), 0) IT_SAL, NVL((select SUM(e.salary) from employees e where e.department_id = d.department_id and e.job_id = 'AD_VP'), 0) VP_SAL, NVL((select SUM(e.salary) from employees e where e.department_id = d.department_id and e.job_id = 'FI_ACCOUNT'), 0) FI_SAL, NVL((select SUM(e.salary) from employees e where e.department_id = d.department_id and e.job_id = 'PU_CLERK'), 0) PU_SAL from departments d改写后的：
select d.department_id, d.department_name, d.location_id, nvl(c.it_sal1,0) it_sal , nvl(c.vp_sal1,0) vp_sal , nvl(c.fi_sal1,0) fi_sal , nvl(c.pu_sal1,0) pu_salfrom departments d left join (select sum(case when e.job_id='IT_PROG' then e.salary end) it_sal1 , sum(case when e.job_id='AD_VP' then e.salary end) vp_sal1 , sum(case when e.job_id='FI_ACCOUNT' then e.salary end) fi_sal1 , sum(case when e.job_id='PU_CLERK' then e.salary end) pu_sal1, e.department_id from employees e group by e.department_id) c on d.department_id=c.department_id ; 阅读全文
1 0
- 标量子查询SQL改写
- SQL优化-标量子查询的改写
- 标量子查询改写
- SQL改写， 聚合语句， left join 改写标量子查询
- 标量子查询SQL改写一则（包括WITH的改写）
- 【SQL改写】notexists-leftjoin(distinct)whereisnull改写_标量子查询
- sql 标量子查询
- 不等值标量子查询改写
- left outer join 改写标量子查询
- oracle标量子查询简介和表连接改写
- 标量子查询
- 标量子查询
- 标量子查询
- 标量子查询
- 标量子查询
- oracle标量子查询
- [Oracle]高效的PL/SQL程序设计(二)--标量子查询
- [Oracle]高效的PL/SQL程序设计(二)--标量子查询
- 什么是 Apache Spark？大数据分析平台如是说
- 大数据早报：「中科点击」想深度布局大数据的分行业应用 万国数据将为阿里巴巴新建数据中心园区（11.29）
- 转载-面试必考的计算机网络知识点梳理
- 商机 | 大数据/政务云采购清单 招标4起，最高招标价为679.68万（11.25-11.28）
- 让熟透的UBUNTU见鬼吧，我们要用OPENSUSE！
- 标量子查询SQL改写
- 关于echarts使用
- Unity FlatShadow阴影技术
- window 10 在myeclipse10 版本 下搭建svn 遇到 的问题 could not generate DH keypair
- Lintcode165 Merge Two Sorted Lists solution 题解
- 第十周项目1 验证算法1
- 外部变量
- mysql自增ID起始值修改方法
- 唯快不破：UDP套接字调用connect()函数
