1、SQL 等價改寫優化案例精選一統一天下解釋為：巧用各類技巧合并代碼，減少表掃描次數。案例 1 注重 COUNT(CASE WHEN) 之類的合并技巧 select distinct ne_state.peer_id peer_name, to_char(ne_state.ne_state) peer_state, (casewhen ne_state.ne_state = 0 thento_char(0)else(select distinct to_char(nvl(ne_active.active, 0)from dcc_sys_log,(select peer_id,decode(act

2、ion,'active',1,'de-active',0,0) active, max(log_time) from dcc_sys_log where action = 'active' or action = 'de-active'group by (peer_id, action) ne_activewhere dcc_sys_log.peer_id = ne_active.peer_id(+) and dcc_sys_log.peer_id = ne_state.peer_id) end) peer_active,(cas

3、ewhen ne_state.ne_state = 0 then to_char(0)else(to_char(nvl(select count(*) from dcc_ne_logwhere dcc_ne_log.result <> 1and peer_id = ne_state.peer_idand log_time betweentrunc(sysdate) and sysdategroup by (peer_id),0)end) err_cnt,(casewhen ne_state.ne_state = 0 thento_char(0)else(to_char(nvl(se

4、lect count(*)from dcc_ne_log in_dnlwhere in_dnl.direction = 'recv'and in_dnl.peer_id =ne_state.peer_idand log_time betweentrunc(sysdate) and sysdate),0)end) recv_cnt,(casewhen ne_state.ne_state = 0 thento_char(0)else(to_char(nvl(select sum(length)from dcc_ne_log in_dnlwhere in_dnl.direction

5、= 'recv'and in_dnl.peer_id =ne_state.peer_idand log_time betweentrunc(sysdate) and sysdate),0)end) recv_byte,(casewhen ne_state.ne_state = 0 thento_char(0)else(to_char(nvl(select count(*)from dcc_ne_log in_dnlwhere in_dnl.direction = 'send'and in_dnl.peer_id =ne_state.peer_idand log_

6、time betweentrunc(sysdate) and sysdate),0)end) send_cnt,(casewhen ne_state.ne_state = 0 thento_char(0)else(to_char(nvl(select sum(length)from dcc_ne_log in_dnlwhere in_dnl.direction = 'send'and in_dnl.peer_id =ne_state.peer_idand log_time betweentrunc(sysdate) and sysdate),0)end) send_bytefr

7、om dcc_ne_log,(select distinct dsl1.peer_id peer_id,nvl(ne_disconnect_info.ne_state, 1) ne_state from dcc_sys_log dsl1,(select distinct dnl.peer_id peer_id,decode(action,'disconnect',0,'connect',0,1) ne_statefrom dcc_sys_log dsl, dcc_ne_log dnlwhere dsl.peer_id = dnl.peer_idand (dsl.

8、action = 'disconnect' anddsl.cause = '關閉對端 ') or(dsl.action = 'connect' anddsl.cause = '連接主機失敗 ')and dsl.log_time =(select max(log_time)from dcc_sys_logwhere peer_id = dnl.peer_idand log_type = ' 對端交互 ') ne_disconnect_infowhere dsl1.peer_id = ne_disconnect_inf

9、o.peer_id(+) ne_state where ne_state.peer_id = dcc_ne_log.peer_id(+)該 SQL 語句復雜冗長，從執行計劃可看出該語句執行了多次的表掃描和復雜表連接，讀者應該可以看出，當前主要的優化方案在于如何等價改寫SQL 以減少表掃描次數。首先想到本案例中為構造出err_cnt ， recv_cnt, recv_byte, send_cnt,send_byte 這 5 個字段而完成的5 個 SQL 結果集能否簡化。 由于這些字段都來自同一張 dcc_ne_log 表，僅是條件不同而已，因此可考慮用 CASE 語句改造代碼，將原來 5 個結果

10、集的寫法改造成 1 個結果集的寫法，這樣 dcc_ne_log 這個大表掃描次數就由 5 次縮減為 1 次了。經適當轉換，這構造 5 個字段的代碼可改寫如下：select peer_id,COUNT(CASE WHEN RESULT <> 1 THEN 1 END) err_cnt ,COUNT(CASE WHEN direction = 'recv' THEN 1 END) recv_cnt,SUM(CASE WHEN direction = 'recv' THEN length END) recv_byte,COUNT(CASE WHEN dir

11、ection = 'send' THEN 1 END) send_cnt,SUM(CASE WHEN direction = 'send' THEN length END) send_bytefrom dcc_ne_logwhere log_time >=trunc(sysdate)GROUP BY peer_id進一步分析如下ne_disconnect_info 結果集的寫法：select distinct dsl1.peer_id peer_id,nvl(ne_disconnect_info.ne_state, 1) ne_statefrom dcc_

12、sys_log dsl1,(select distinct dnl.peer_id peer_id,decode(action,'disconnect',0,'connect',0,1) ne_statefrom dcc_sys_log dsl, dcc_ne_log dnlwhere dsl.peer_id = dnl.peer_idand (dsl.action = 'disconnect' anddsl.cause = '關閉對端 ') or(dsl.action = 'connect' anddsl.cau

13、se = '連接主機失敗 ')and log_type = ' 對端交互 'and dsl.log_time =(select max(log_time)from dcc_sys_logwhere peer_id = dnl.peer_idand log_type = ' 對端交互 ') ne_disconnect_info where dsl1.peer_id = ne_disconnect_info.peer_id(+)為了清晰的將結構展現出來，將上述兩次改造的腳本用 WITH 子句分別封裝為 ne_state和 dcc_ne_log_tim

14、e ，最終整體SQL 語句改寫完畢后，代碼量大大減少的同時性能極大的提升了，表掃描次數從總計 7 次縮減為 2 次，執行完成時間從原先的 300 秒縮短為 5 秒。完整改造后的最終SQL 代碼優雅精致，具體如下：with ne_state as(SELECT a.peer_id,CASE WHEN dnl.peer_id IS NOT NULL AND str IN ('disconnect 關閉對端 ','connect 連接主機失敗 ') THEN '0'ELSE'1' END ne_stateFROM (SELECT pee

15、r_id,MIN(action|cause) KEEP(DENSE_RANK LAST ORDER BY log_time) strFROM dcc_sys_log dslWHERE log_type = ' 對端交互 'GROUP BY peer_id) a,(SELECT DISTINCT peer_id FROM dcc_ne_log) dnl WHERE a.peer_id = dnl.peer_id(+), dcc_ne_log_time as (select peer_id,COUNT(CASE WHEN RESULT <> 1 THEN 1 END)

16、err_cnt ,COUNT(CASE WHEN direction = 'recv' THEN 1 END) recv_cnt,SUM(CASE WHEN direction = 'recv' THEN length END) recv_byte,COUNT(CASE WHEN direction = 'send' THEN 1 END) send_cnt,SUM(CASE WHEN direction = 'send' THEN length END) send_bytefrom dcc_ne_logwhere log_tim

17、e>=trunc(sysdate) -between trunc(sysdate)and sysdateGROUP BY peer_id)select distinct ne_state.peer_id peer_name, to_char(ne_state.ne_state) peer_state, (casewhen ne_state.ne_state = 0 thento_char(0)elseNVL(select '1' from dcc_sys_log where peer_id = ne_state.peer_id and action ='activ

18、e' and rownum=1),'0')end) peer_active,decode(ne_state.ne_state,0,'0',nvl(dnlt.ERR_CNT,0) ERR_CNT, -注意 NVL 改造decode(ne_state.ne_state,0,'0',nvl(dnlt.recv_cnt,0) recv_cnt, decode(ne_state.ne_state,0,'0',nvl(dnlt.recv_byte,0) recv_byte, decode(ne_state.ne_state,0,

19、9;0',nvl(dnlt.send_cnt,0) send_cnt, decode(ne_state.ne_state,0,'0',nvl(dnlt.send_byte,0) send_byte from ne_state ,dcc_ne_log_time dnlt where ne_state.peer_id=dnlt.peer_id(+)案例 2. 注重分析函數減少表掃描次數，如 KEEP 和 DENSE_RANK 的結合這個例子來自案例 1 中，為了說明技巧，特定再次獨立成一小節說明，案例 1 中的如下部分ne_disconnect_info 結果集的寫法：se

20、lect distinct dsl1.peer_id peer_id,nvl(ne_disconnect_info.ne_state, 1) ne_statefrom dcc_sys_log dsl1,(select distinct dnl.peer_id peer_id,decode(action,'disconnect',0,'connect',0,1) ne_statefrom dcc_sys_log dsl, dcc_ne_log dnlwhere dsl.peer_id = dnl.peer_idand (dsl.action = 'disc

21、onnect' anddsl.cause = '關閉對端 ') or(dsl.action = 'connect' anddsl.cause = '連接主機失敗 ')and log_type = ' 對端交互 'and dsl.log_time =(select max(log_time)from dcc_sys_logwhere peer_id = dnl.peer_idand log_type = ' 對端交互 ') ne_disconnect_info where dsl1.peer_id = ne_

22、disconnect_info.peer_id(+)原先至少需要掃描 2 次！現在根據 KEEP 結合 DENSE_RANK 的方式，將表掃描從 2 次變為了 1 次，具體代碼改寫如下：SELECT a.peer_id,CASE WHEN dnl.peer_id IS NOT NULL AND str IN ('disconnect 關閉對端 ','connect 連接主機失敗 ') THEN'0' ELSE '1' END ne_stateFROM(SELECTpeer_id,MIN(action|cause)KEEP(DENS

23、E_RANK LAST ORDER BY log_time) strFROM dcc_sys_log dslWHERE log_type = ' 對端交互 'GROUP BY peer_id) a,(SELECT DISTINCT peer_id FROM dcc_ne_log) dnl WHERE a.peer_id = dnl.peer_id(+)案例 3 注重 GROUP BY (CASE WHEN) 之類的合并技巧 select decode(so.sFileName, 'SNP_20', 'SNP', 'HNIC_2',

24、 'HNIC', 'IBRC_2', 'IBRC', 'IISMP_', 'IISMP', 'NIC_20', 'NIC','NIG_20', 'NIG', 'IIC_20', 'IIC', 'HIIC_2', 'HIIC', 'CA.D.A', 'CA.D.ATSR', 'ULH_20', 'ULH', 'IBRST

25、_', 'IBRST', so.sFileName) 業務名稱 ,so.sFileCount 合并前文件個數 , so.sRecordNum 合并前總記錄數 , ta.tFileCount 合并后文件個數 , ta.tRecordNum 合并后總記錄數 , NVL(so1.sFileCount, 0) 合并前當天文件個數 ,NVL(so1.sRecordNum, 0) 合并前當天文件總記錄數 , NVL(so2.sFileCount, 0) 合并前昨天文件個數 , NVL(so2.sRecordNum, 0) 合并前昨天文件總記錄數 from (select subst

26、r(a.file_name, 1, 6) sfileName, count(*) sFileCount,sum(sRecordNum) sRecordNumfrom (select distinct bsf.file_name,bsf.record_num sRecordNum,bsf.create_timefrom BUSINESS_SEND_FILELIST bsf, IDEP_PLUGIN_AUTO_RELATION iparwhere bsf.file_id = ipar.bus_file_idand trunc(ipar.relation_time) = to_date('2

27、010-08-05', 'yyyy-mm-dd') agroup by substr(a.file_name, 1, 6)order by substr(a.file_name, 1, 6) soLEFT JOIN(select substr(a.file_name, 1, 6) sfileName,count(*) sFileCount,sum(sRecordNum) sRecordNumfrom (select distinct bsf.file_name,bsf.record_num sRecordNum,bsf.create_timefrom BUSINESS_

28、SEND_FILELIST bsf, IDEP_PLUGIN_AUTO_RELATION ipar where bsf.file_id = ipar.bus_file_idand trunc(ipar.relation_time) = to_date('2010-08-05', 'yyyy-mm-dd')AND FILE_NAME LIKE '%20100805%') a group by substr(a.file_name, 1, 6) order by substr(a.file_name, 1, 6) so1 ON (so.sFileNa

29、me = so1.sFileName) LEFT JOIN(select substr(a.file_name, 1, 6) sfileName,count(*) sFileCount,sum(sRecordNum) sRecordNumfrom (select distinct bsf.file_name,bsf.record_num sRecordNum,bsf.create_timefrom BUSINESS_SEND_FILELIST bsf, IDEP_PLUGIN_AUTO_RELATION ipar where bsf.file_id = ipar.bus_file_idand

30、trunc(ipar.relation_time) = to_date('2010-08-05', 'yyyy-mm-dd')AND FILE_NAME not LIKE '%20100805%') a group by substr(a.file_name, 1, 6)order by substr(a.file_name, 1, 6) so2ON (so.sFileName = so2.sFileName)LEFT JOIN(select substr(a.file_name, 1, 6) tFileName, count(*) tFileC

31、ount,sum(record_num) tRecordNumfrom (select distinct ipsf.file_name,ipsf.record_numfrom idep_plugin_send_filelist ipsfwhere trunc(ipsf.create_time) = to_date('2010-08-05', 'yyyy-mm-dd')and remark = '處理成功 ') agroup by substr(a.file_name, 1, 6)order by substr(a.file_name, 1, 6)

32、 taON (so.sFileName = ta.tFileName)where so.sFileName not like 'MVI%'unionselect so.sFileName,so.sFileCount,(so.sRecordNum - (so.sFileCount * 2) sRecordNum,ta.tFileCount,ta.tRecordNum,NVL(so1.sFileCount, 0),(nvl(so1.sRecordNum, 0) - (nvl(so1.sFileCount, 0) * 2), NVL(so2.sFileCount, 0),(nvl(s

33、o2.sRecordNum, 0) - (nvl(so2.sFileCount, 0) * 2) from (select substr(a.file_name, 1, 3) sfileName, count(*) sFileCount,sum(sRecordNum) sRecordNumfrom (select distinct bsf.file_name,bsf.record_num sRecordNum,bsf.create_timefrom BUSINESS_SEND_FILELIST bsf, IDEP_PLUGIN_AUTO_RELATION iparwhere bsf.file_

34、id = ipar.bus_file_idand trunc(ipar.relation_time) = to_date('2010-08-05', 'yyyy-mm-dd') agroup by substr(a.file_name, 1, 3)order by substr(a.file_name, 1, 3) soLEFT JOIN(select substr(a.file_name, 1, 3) sfileName,count(*) sFileCount,sum(sRecordNum) sRecordNumfrom (select distinct bs

35、f.file_name,bsf.record_num sRecordNum,bsf.create_timefrom BUSINESS_SEND_FILELIST bsf, IDEP_PLUGIN_AUTO_RELATION ipar where bsf.file_id = ipar.bus_file_idand trunc(ipar.relation_time) = to_date('2010-08-05', 'yyyy-mm-dd')AND FILE_NAME LIKE 'MVI100805%') a group by substr(a.fil

36、e_name, 1, 3) order by substr(a.file_name, 1, 3) so1 ON (so.sFileName = so1.sFileName)LEFT JOIN(select substr(a.file_name, 1, 3) sfileName,count(*) sFileCount,sum(sRecordNum) sRecordNumfrom (select distinct bsf.file_name,bsf.record_num sRecordNum,bsf.create_timefrom BUSINESS_SEND_FILELIST bsf, IDEP_

37、PLUGIN_AUTO_RELATION ipar where bsf.file_id = ipar.bus_file_idand trunc(ipar.relation_time) = to_date('2010-08-05', 'yyyy-mm-dd')AND FILE_NAME not LIKE 'MVI100805%') a group by substr(a.file_name, 1, 3) order by substr(a.file_name, 1, 3) so2ON (so.sFileName = so2.sFileName)LE

38、FT JOIN(select substr(a.file_name, 1, 3) tFileName,count(*) tFileCount,sum(record_num) tRecordNumfrom (select distinct ipsf.file_name,ipsf.record_numfrom idep_plugin_send_filelist ipsfwhere trunc(ipsf.create_time) = to_date('2010-08-05', 'yyyy-mm-dd')and remark = '處理成功 ') agr

39、oup by substr(a.file_name, 1, 3)order by substr(a.file_name, 1, 3) taON (so.sFileName = ta.tFileName)WHERE so.sFileName = 'MVI'分析1.將trunc(ipar.relation_time)=to_date('2010-08-05','yyyy-mm-dd')等等類似之處改寫為如下，要避免對列進行運算，這樣會導致用不上索引，除非是建立了函數索引。ipsf.create_time >= to_date('2010

40、-08-05', 'yyyy-mm-dd') and ipsf.create_time < to_date('2010-08-05', 'yyyy-mm-dd')+12可通過CASE WHEN語句進一步減少表掃描次數，如（ count(CASE WHEN FILE_NAME LIKE'%20100805%' THEN 1 END) sFileCount1），類似如上的修改，可以等價改寫，將本應用的表掃描從8 次減少為4 次。3. 更進一步 利用 group by substr(a.file_name, 1, CASE

41、 WHEN a.file_name like 'MVI%' THEN 3ELSE 6 END) ，可將表掃描從4 次再次減少為2 次最終 SQL 改寫優化為：select decode(so.sFileName, 'SNP_20', 'SNP', 'HNIC_2', 'HNIC', 'IBRC_2', 'IBRC', 'IISMP_', 'IISMP', 'NIC_20', 'NIC','NIG_20',

42、 'NIG', 'IIC_20', 'IIC', 'HIIC_2', 'HIIC', 'CA.D.A', 'CA.D.ATSR', 'ULH_20', 'ULH', 'IBRST_', 'IBRST', so.sFileName) 業務名稱 ,so.sFileCount 合并前文件個數,case when so.sfilename like 'MVI%' then (so.sRecordNum - (so

43、.sFileCount * 2) else so.sRecordNum end合并前總記錄數,ta.tFileCount 合并后文件個數 , ta.tRecordNum 合并后總記錄數 , NVL(so.sFileCount1, 0) 合并前當天文件個數 , case when so.sfilename like 'MVI%'then (nvl(so.sRecordNum1, 0) - (nvl(so.sFileCount1, 0) * 2)else NVL(so.sRecordNum1, 0) end 合并前當天文件總記錄數 , NVL(so.sFileCount2, 0)

44、合并前昨天文件個數 , case when so.sfilename like 'MVI%'then (nvl(so.sRecordNum2, 0) - (nvl(so.sFileCount2, 0) * 2) else NVL(so.sRecordNum2, 0) end 合并前昨天文件總記錄數 from (select substr(a.file_name, 1, CASE WHEN a.file_name like 'MVI%' THEN 3 ELSE 6 END) sfileName,count(*) sFileCount,sum(sRecordNum)

45、 sRecordNum,count(CASE WHEN (FILE_NAME LIKE '%20100805%' AND FILE_NAME not like 'MVI%') OR(FILE_NAME LIKE 'MVI100805%' AND FILE_NAME like 'MVI%') THEN 1 END) sFileCount1,sum (CASE WHEN (FILE_NAME LIKE '%20100805%' AND FILE_NAME not like 'MVI%') OR(FILE

46、_NAME LIKE 'MVI100805%' AND FILE_NAME like 'MVI%') THEN sRecordNum END) sRecordNum1, count(CASE WHEN (FILE_NAME NOT LIKE '%20100805%' AND FILE_NAME not like 'MVI%') OR(FILE_NAME NOT LIKE 'MVI100805%' AND FILE_NAME like 'MVI%') THEN 1 END) sFileCount2,s

47、um (CASE WHEN (FILE_NAME NOT LIKE '%20100805%' AND FILE_NAME not like 'MVI%') OR(FILE_NAME NOT LIKE 'MVI100805%' AND FILE_NAME like 'MVI%') THEN sRecordNum END) sRecordNum2from (select distinct bsf.file_name,bsf.record_num sRecordNum,bsf.create_timefrom BUSINESS_SEND_

48、FILELIST bsf, IDEP_PLUGIN_AUTO_RELATION iparwhere bsf.file_id = ipar.bus_file_idand ipar.relation_time >= to_date('2010-08-05', 'yyyy-mm-dd') and ipar.relation_time < to_date('2010-08-05', 'yyyy-mm-dd')+1) agroup by substr(a.file_name, 1, CASE WHEN a.file_name l

49、ike 'MVI%' THEN 3 ELSE 6 END)order by substr(a.file_name, 1, CASE WHEN a.file_name like 'MVI%' THEN 3 ELSE 6 END) so LEFT JOIN(select substr(a.file_name, 1, CASE WHEN a.file_name like 'MVI%' THEN 3 ELSE 6 END) tFileName,count(*) tFileCount,sum(record_num) tRecordNumfrom (sele

50、ct distinct ipsf.file_name,ipsf.record_numfrom idep_plugin_send_filelist ipsfwhere ipsf.create_time >= to_date('2010-08-05', 'yyyy-mm-dd') and ipsf.create_time < to_date('2010-08-05', 'yyyy-mm-dd')+1 and remark = '處理成功 ') agroup by substr(a.file_name, 1,

51、 CASE WHEN a.file_name like 'MVI%' THEN 3 ELSE 6 END)order by substr(a.file_name, 1, CASE WHEN a.file_name like 'MVI%' THEN 3 ELSE 6 END) taON (so.sFileName = ta.tFileName)案例 4 尋找用 or 替代 union all 的時機 select peer_id 對端標識 ,null 源域名 ,null 目標域名 ,alert_type 告警類型 ,log_time 告警時間 ,cause 告警內

52、容 ,deal_log 處理狀態 ,deal_staff 處理人 ,deal_time 處理時間 ,remark 備注from dcc_sys_logwhere action = 'disconnect'and cause like '對端被關閉 %'and deal_log = 'deal_log'and alert_type = 'alert_type'and log_time >= TO_DATE('2010-08-02', 'YYYY-MM-DD') and log_time <

53、 TO_DATE('2010-08-03', 'YYYY-MM-DD') + 1 union(select peer_id 對端標識 ,origin_host源域名 ,dest_host 目標域名 ,alert_type 告警類型 ,log_time 告警時間 ,cause 告警內容 ,deal_log 處理狀態 ,deal_staff 處理人 ,deal_time 處理時間 ,remark 備注from dcc_ne_logwhere result = 0and cause like 'parser 失敗 %'and deal_log = &#

54、39;deal_log'and alert_type = 'alert_type'and log_time >= TO_DATE('2010-08-02', 'YYYY-MM-DD') and log_time < TO_DATE('2010-08-03', 'YYYY-MM-DD') + 1) union(select peer_id 對端標識 ,origin_host源域名 ,dest_host 目標域名 ,alert_type 告警類型 ,log_time 告警時間 ,cause 告警內容

55、 ,deal_log 處理狀態 ,deal_staff 處理人 ,deal_time 處理時間 ,remark 備注from dcc_ne_logwhere result_code = 'DIAMETER_UNABLE_TO_DELIVER' and svcctx_id like 'SR-Timeout%'and deal_log = 'deal_log'and alert_type = 'alert_type'and log_time >= TO_DATE('2010-08-02', 'YYYY-M

56、M-DD') and log_time < TO_DATE('2010-08-03', 'YYYY-MM-DD') + 1) union(select peer_id 對端標識 ,null 源域名 ,null 目標域名 ,alert_type 告警類型 ,log_time 告警時間 ,cause 告警內容 ,deal_log 處理狀態 ,deal_staff 處理人 ,deal_time 處理時間 ,remark 備注from dcc_sys_logwhere action = 'disconnect'and cause like

57、'接收消息異常 %'and deal_log = 'deal_log'and alert_type = 'alert_type'and log_time >= TO_DATE('2010-08-02', 'YYYY-MM-DD')and log_time < TO_DATE('2010-08-03', 'YYYY-MM-DD') + 1)很明顯，此處的 UNION ALL 完全可以用 OR 來改造，等價改寫，減少表掃描次數。優化改造后 SQL 為：select peer_

58、id 對端標識 ,null 源域名 ,null 目標域名 ,alert_type 告警類型 ,log_time 告警時間 ,cause 告警內容 ,deal_log 處理狀態 ,deal_staff 處理人 ,deal_time 處理時間 ,remark 備注from dcc_sys_logwhere action = 'disconnect'and (cause like '對端被關閉 %' or cause like ' 接收消息異常 %') and deal_log = 'deal_log'and alert_type =

59、'alert_type'and log_time >= TO_DATE('2010-08-02', 'YYYY-MM-DD') and log_time < TO_DATE('2010-08-03', 'YYYY-MM-DD') + 1 unionselect peer_id 對端標識 ,origin_host源域名 ,dest_host 目標域名 ,alert_type 告警類型 ,log_time 告警時間 ,cause 告警內容 ,deal_log 處理狀態 ,deal_staff 處理人 ,deal_time 處理時間