一网友说下面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_OUTPUT
Plan 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_OUTPUT Plan 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_sal from 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 ;