各位好！
SQL审核优化是专项服务部的一项业务，这项业务在很多同事眼里，可能会觉得这是一个很高难度的事情，原因之一是对SQL并不熟悉，另外一个原因则可能是被我之前为了做SQL培训而出的题给吓到了（其实，交卷同学的平均分也就是40来分，更何况大家工作年限擅长领域也不一样，所以得分低也不用太沮丧）。
而实际上，SQL审核优化并不是一项完完全全很高深的工作，至少目前实际服务过的几家客户给我的感觉是这样的，因为需要用到高深的SQL知识去优化SQL的场景实际上并不多，大多还是通过非常基本的优化手段就可以解决SQL的性能问题。
由于经常出差，没有整块的时间来整理并录制SQL审核视频，所以我将要作为SQL审核培训的内容，一章一节的先通过邮件列表的形式发出来。写这部分内容的目的，是希望能通过有针对性的知识点或案例讲解，让大家能很快认识并入门SQL审核工作。大家在看的时候如有意见也请不吝提出。人生头一次写网络连载，不定期更新，所以，请勿催，请轻拍。
——————————————————————————————————————————————
今天，先来一个简单的案例，给大家开胃（东北菜，量实在）。
SQL原文：
——————————————————————————————————————————
select '数量' 类型 ,to_char(DECODE(SUM(JNC),'',0,SUM(JNC))) 上门维护人员服技能差 ,
to_char(DECODE(SUM(TDC),'',0,SUM(TDC)))上门维护人员服务态度差,
to_char(DECODE(SUM(BNSY),'',0,SUM(BNSY)))安装后不能使用,
to_char(DECODE(SUM(WSM),'',0,SUM(WSM))) 网速慢,
to_char(DECODE(SUM(YDX),'',0,SUM(YDX))) 容易掉线 ,
to_char(DECODE(SUM(BWD),'',0,SUM(BWD))) 网络不稳定 ,
to_char(DECODE(SUM(HJ),'',0,SUM(HJ))) 合计
from (
select case when ft.track_resultcode='JNC' and fa.vistisacceptvist='Y'
THEN 1 ELSE 0 END AS JNC,
case when ft.track_resultcode='TDC' and fa.vistisacceptvist='Y'
THEN 1 ELSE 0 END AS TDC,
case when ft.track_resultcode='BNSY' and fa.vistisacceptvist='Y'
THEN 1 ELSE 0 END AS BNSY,
case when ft.track_resultcode='WSM' and fa.vistisacceptvist='Y'
THEN 1 ELSE 0 END AS WSM,
case when ft.track_resultcode='YDX' and fa.vistisacceptvist='Y'
THEN 1 ELSE 0 END AS YDX,
case when ft.track_resultcode='BWD' and fa.vistisacceptvist='Y'
THEN 1 ELSE 0 END AS BWD,
case when ft.track_resultcode
IN('JNC','TDC','BNSY','WSM','YDX','BWD') and fa.vistisacceptvist='Y' THEN 1
ELSE 0 END AS HJ
from wo_work_order t,fault_archives fa,fault_order_visit_track ft
where t.tache_code IN ( 'AZ_RG_HF')
AND T.WORK_ORDER_STATE = '10F'
and t.base_order_id=fa.fault_archive_id
and t.base_order_id=ft.orderid
and ft.questionid = 14
AND T.FINISH_DATE between /*trunc(sysdate - 1)*/
to_date('20150527', 'yyyymmdd')
and trunc(sysdate) ) t1
UNION ALL
select '占比' 类型
,DECODE(decode(SUM(HJ),'',0,SUM(HJ)),0,'--',trunc(decode(SUM(JNC),'',0,SUM(JNC))/decode(SUM(HJ),'',0,SUM(HJ))*100,2)||'%')
上门维护人员服技能差 ,
DECODE(decode(SUM(HJ),'',0,SUM(HJ)),0,'--',trunc(decode(SUM(TDC),'',0,SUM(TDC))/decode(SUM(HJ),'',0,SUM(HJ))*100,2)||'%')
上门维护人员服务态度差,
DECODE(decode(SUM(HJ),'',0,SUM(HJ)),0,'--',trunc(decode(SUM(BNSY),'',0,SUM(BNSY))/decode(SUM(HJ),'',0,SUM(HJ))*100,2)||'%')
安装后不能使用,
DECODE(decode(SUM(HJ),'',0,SUM(HJ)),0,'--',trunc(decode(SUM(WSM),'',0,SUM(WSM))/decode(SUM(HJ),'',0,SUM(HJ))*100,2)||'%')
网速慢,
DECODE(decode(SUM(HJ),'',0,SUM(HJ)),0,'--',trunc(decode(SUM(YDX),'',0,SUM(YDX))/decode(SUM(HJ),'',0,SUM(HJ))*100,2)||'%')
容易掉线 ,
DECODE(decode(SUM(HJ),'',0,SUM(HJ)),0,'--',trunc(decode(SUM(BWD),'',0,SUM(BWD))/decode(SUM(HJ),'',0,SUM(HJ))*100,2)||'%')
网络不稳定 ,
'--' 合计
from (
select case when ft.track_resultcode='JNC' and fa.vistisacceptvist='Y'
THEN 1 ELSE 0 END AS JNC,
case when ft.track_resultcode='TDC' and fa.vistisacceptvist='Y'
THEN 1 ELSE 0 END AS TDC,
case when ft.track_resultcode='BNSY' and fa.vistisacceptvist='Y'
THEN 1 ELSE 0 END AS BNSY,
case when ft.track_resultcode='WSM' and fa.vistisacceptvist='Y'
THEN 1 ELSE 0 END AS WSM,
case when ft.track_resultcode='YDX' and fa.vistisacceptvist='Y'
THEN 1 ELSE 0 END AS YDX,
case when ft.track_resultcode='BWD' and fa.vistisacceptvist='Y'
THEN 1 ELSE 0 END AS BWD,
case when ft.track_resultcode
IN('JNC','TDC','BNSY','WSM','YDX','BWD') and fa.vistisacceptvist='Y' THEN 1
ELSE 0 END AS HJ
from wo_work_order t,fault_archives fa,fault_order_visit_track ft
where t.tache_code IN ( 'AZ_RG_HF')
AND T.WORK_ORDER_STATE = '10F'
and t.base_order_id=fa.fault_archive_id
and t.base_order_id=ft.orderid
and ft.questionid = 14
AND T.FINISH_DATE between /*trunc(sysdate - 1)*/
to_date('20150527', 'yyyymmdd')
and trunc(sysdate) ) t2
——————————————————————————————————————————
单纯看这个SQL，你会觉得比较晕么？的确，对于一个写得较为复杂的SQL而言，想一眼看出来该SQL是干什么的，不是一件容易的事情。那这种时候我们可以看看执行计划：
——————————————————————————————————————————
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows |
Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 |
88 | 20 (50)| 00:00:01 | | 1 | UNION-ALL |
| | | | | | 2 | SORT
AGGREGATE | | 1 | 44 |
| | |* 3 | FILTER |
| | | | | | 4 | NESTED LOOPS
| | | | | | |
5 | NESTED LOOPS | | 1 | 44
| 10 (0)| 00:00:01 | | 6 | NESTED LOOPS |
| 1 | 36 | 8 (0)| 00:00:01 | |* 7 | TABLE
ACCESS FULL | FAULT_ORDER_VISIT_TRACK | 1 | 11 | 3 (0)|
00:00:01 | |* 8 | TABLE ACCESS BY INDEX ROWID| WO_WORK_ORDER
| 1 | 25 | 5 (0)| 00:00:01 | |* 9 | INDEX RANGE SCAN
| IDX_WO_WORK_BASEID | 3 | | 2 (0)| 00:00:01 | |* 10 |
INDEX UNIQUE SCAN | PK_FAULT_ARCHIVES | 1 | |
1 (0)| 00:00:01 | | 11 | TABLE ACCESS BY INDEX ROWID |
FAULT_ARCHIVES | 1 | 8 | 2 (0)| 00:00:01 | | 12 |
SORT AGGREGATE | | 1 | 44 |
| | |* 13 | FILTER |
| | | | | | 14 | NESTED LOOPS
| | | | | |
| 15 | NESTED LOOPS | | 1 |
44 | 10 (0)| 00:00:01 | | 16 | NESTED LOOPS |
| 1 | 36 | 8 (0)| 00:00:01 | |* 17 |
TABLE ACCESS FULL | FAULT_ORDER_VISIT_TRACK | 1 | 11 | 3
(0)| 00:00:01 | |* 18 | TABLE ACCESS BY INDEX ROWID| WO_WORK_ORDER
| 1 | 25 | 5 (0)| 00:00:01 | |* 19 | INDEX RANGE SCAN
| IDX_WO_WORK_BASEID | 3 | | 2 (0)| 00:00:01 | |*
20 | INDEX UNIQUE SCAN | PK_FAULT_ARCHIVES | 1 |
| 1 (0)| 00:00:01 | | 21 | TABLE ACCESS BY INDEX ROWID |
FAULT_ARCHIVES | 1 | 8 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
--------------------------------------------------- 3 - filter(TO_DATE('
2015-05-27 00:00:00', 'syyyy-mm-dd hh24:mi:ss')<=TRUNC(SYSDATE@!)) 7 -
filter("FT"."QUESTIONID"=14) 8 - filter("T"."FINISH_DATE">=TO_DATE('
2015-05-27 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"T"."TACHE_CODE"='AZ_RG_HF' AND "T"."WORK_ORDER_STATE"='10F' AND
"T"."FINISH_DATE"<=TRUNC(SYSDATE@!)) 9 -
access("T"."BASE_ORDER_ID"="FT"."ORDERID") 10 -
access("T"."BASE_ORDER_ID"="FA"."FAULT_ARCHIVE_ID") 13 - filter(TO_DATE('
2015-05-27 00:00:00', 'syyyy-mm-dd hh24:mi:ss')<=TRUNC(SYSDATE@!)) 17 -
filter("FT"."QUESTIONID"=14) 18 - filter("T"."FINISH_DATE">=TO_DATE('
2015-05-27 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"T"."TACHE_CODE"='AZ_RG_HF' AND "T"."WORK_ORDER_STATE"='10F' AND
"T"."FINISH_DATE"<=TRUNC(SYSDATE@!)) 19 -
access("T"."BASE_ORDER_ID"="FT"."ORDERID") 20 -
access("T"."BASE_ORDER_ID"="FA"."FAULT_ARCHIVE_ID")
——————————————————————————————————————————
从执行计划上，我们发现，UNION
ALL前后的两个select语句，其内层查询的执行计划是完全一样的，我们有理由怀疑其内层查询是完全一样的，于是乎，比对了两个内层查询的SQL文本，确认了两个内层查询是完全一模一样的。这样的写法，会让Oracle对同一SQL执行两次，导致重复计算，所以，提高性能就需要我们消除重复计算，我们做以下修改：
——————————————————————————————————————————with t as(select case when
ft.track_resultcode='JNC' and fa.vistisacceptvist='Y' THEN 1 ELSE 0 END AS JNC,
case when ft.track_resultcode='TDC' and fa.vistisacceptvist='Y'
THEN 1 ELSE 0 END AS TDC,
case when ft.track_resultcode='BNSY' and fa.vistisacceptvist='Y'
THEN 1 ELSE 0 END AS BNSY,
case when ft.track_resultcode='WSM' and fa.vistisacceptvist='Y'
THEN 1 ELSE 0 END AS WSM,
case when ft.track_resultcode='YDX' and fa.vistisacceptvist='Y'
THEN 1 ELSE 0 END AS YDX,
case when ft.track_resultcode='BWD' and fa.vistisacceptvist='Y'
THEN 1 ELSE 0 END AS BWD,
case when ft.track_resultcode
IN('JNC','TDC','BNSY','WSM','YDX','BWD') and fa.vistisacceptvist='Y' THEN 1
ELSE 0 END AS HJ
from wo_work_order t,fault_archives fa,fault_order_visit_track ft
where t.tache_code IN ( 'AZ_RG_HF')
AND T.WORK_ORDER_STATE = '10F'
and t.base_order_id=fa.fault_archive_id
and t.base_order_id=ft.orderid
and ft.questionid = 14
AND T.FINISH_DATE between /*trunc(sysdate - 1)*/
to_date('20150527', 'yyyymmdd')
and trunc(sysdate))
select '数量' 类型 ,to_char(DECODE(SUM(JNC),'',0,SUM(JNC))) 上门维护人员服技能差 ,
to_char(DECODE(SUM(TDC),'',0,SUM(TDC)))上门维护人员服务态度差,
to_char(DECODE(SUM(BNSY),'',0,SUM(BNSY)))安装后不能使用,
to_char(DECODE(SUM(WSM),'',0,SUM(WSM))) 网速慢,
to_char(DECODE(SUM(YDX),'',0,SUM(YDX))) 容易掉线 ,
to_char(DECODE(SUM(BWD),'',0,SUM(BWD))) 网络不稳定 ,
to_char(DECODE(SUM(HJ),'',0,SUM(HJ))) 合计
from t
UNION ALL
select '占比' 类型
,DECODE(decode(SUM(HJ),'',0,SUM(HJ)),0,'--',trunc(decode(SUM(JNC),'',0,SUM(JNC))/decode(SUM(HJ),'',0,SUM(HJ))*100,2)||'%')
上门维护人员服技能差 ,
DECODE(decode(SUM(HJ),'',0,SUM(HJ)),0,'--',trunc(decode(SUM(TDC),'',0,SUM(TDC))/decode(SUM(HJ),'',0,SUM(HJ))*100,2)||'%')
上门维护人员服务态度差,
DECODE(decode(SUM(HJ),'',0,SUM(HJ)),0,'--',trunc(decode(SUM(BNSY),'',0,SUM(BNSY))/decode(SUM(HJ),'',0,SUM(HJ))*100,2)||'%')
安装后不能使用,
DECODE(decode(SUM(HJ),'',0,SUM(HJ)),0,'--',trunc(decode(SUM(WSM),'',0,SUM(WSM))/decode(SUM(HJ),'',0,SUM(HJ))*100,2)||'%')
网速慢,
DECODE(decode(SUM(HJ),'',0,SUM(HJ)),0,'--',trunc(decode(SUM(YDX),'',0,SUM(YDX))/decode(SUM(HJ),'',0,SUM(HJ))*100,2)||'%')
容易掉线 ,
DECODE(decode(SUM(HJ),'',0,SUM(HJ)),0,'--',trunc(decode(SUM(BWD),'',0,SUM(BWD))/decode(SUM(HJ),'',0,SUM(HJ))*100,2)||'%')
网络不稳定 ,
'--' 合计
from t
——————————————————————————————————————————
如此改写后，SQL就能少做一次内层查询，由于join的成本是众所周知的高，所以大致可以认为效率几乎提高了一倍。
从这个案例，你应该了解到：
使用with关键字，可以提取一条SQL中的公共查询，减少重复计算，提高SQL性能
with写法，是SQL-99定义的标准，Oracle 从9i开始就支持这种写法
PS：这个案例还可以进一步优化，如果你想出来了，可以回复本邮件，分享给大家，将此SQL的性能提高到更接近一倍的水平。
------------------
Best Regards.
--怀晓明
--------------------------------------------
云和恩墨（北京）信息技术有限公司
Yunhe Enmo（Beijing）Technology Co.,LTD
地址：北京市朝阳区东大桥路8号尚都SOHO北塔B座21层2108
电话：010-59003186
手机: 13911047525
邮箱: xiaoming.huai@xxxxxxxxxxxx
网址: www.enmotech.com
--------------------------------------------
数据驱动 成就未来
--------------------------------------------
