SQL等价改写优化案例精选

1、SQL等价改写优化案例精选一.统一天下解释为：巧用各类技巧合并代码，减少表扫描次数。案例1注重COUNT(CASEWHEN)之类的合并技巧selectdistinctne_state.peer_idpeer_name,to_char(ne_state.ne_state)peer_state,(casewhenne_state.ne_state=0thento_char(0)else(selectdistinctto_char(nvl(ne_active.active,0)fromdcc_sys_log,(selectpeer_id,decode(action,'active',

2、1,'de-active',0,0)active,max(log_time)fromdcc_sys_logwhereaction='active'oraction='de-active'groupby(peer_id,action)ne_activewheredcc_sys_log.peer_id=ne_active.peer_id(+)anddcc_sys_log.peer_id=ne_state.peer_id)end)peer_active,(casewhenne_state.ne_state=0thento_char(0)else(to_

3、char(nvl(selectcount(*)fromdcc_ne_logwheredcc_ne_log.result<>1andpeer_id=ne_state.peer_idandlog_timebetweentrunc(sysdate)andsysdategroupby(peer_id),0)end)err_cnt,(casewhenne_state.ne_state=0thento_char(0)else(to_char(nvl(selectcount(*)fromdcc_ne_login_dnlwherein_dnl.direction='recv'and

4、in_dnl.peer_id=ne_state.peer_idandlog_timebetweentrunc(sysdate)andsysdate),0)end)recv_cnt,(casewhenne_state.ne_state=0thento_char(0)else(to_char(nvl(selectsum(length)fromdcc_ne_login_dnlwherein_dnl.direction='recv'andin_dnl.peer_id=ne_state.peer_idandlog_timebetweentrunc(sysdate)andsysdate),

5、0)end)recv_byte,(casewhenne_state.ne_state=0thento_char(0)else(to_char(nvl(selectcount(*)fromdcc_ne_login_dnlwherein_dnl.direction='send'andin_dnl.peer_id=ne_state.peer_idandlog_timebetweentrunc(sysdate)andsysdate),0)end)send_cnt,(casewhenne_state.ne_state=0thento_char(0)else(to_char(nvl(sel

6、ectsum(length)fromdcc_ne_login_dnlwherein_dnl.direction='send'andin_dnl.peer_id=ne_state.peer_idandlog_timebetweentrunc(sysdate)andsysdate),0)end)send_bytefromdcc_ne_log,(selectdistinctdsl1.peer_idpeer_id,nvl(ne_disconnect_info.ne_state,1)ne_statefromdcc_sys_logdsl1,(selectdistinctdnl.peer_i

7、dpeer_id,decode(action,'disconnect',0,'connect',0,1)ne_statefromdcc_sys_logdsl,dcc_ne_logdnlwheredsl.peer_id=dnl.peer_idand(dsl.action='disconnect'anddsl.cause='关闭端)or(dsl.action='connect'anddsl.cause='连接主机失败')anddsl.log_time=(selectmax(log_time)fromdcc_sy

8、s_logwherepeer_id=dnl.peer_idandlog_type='对端交互')ne_disconnect_infowheredsl1.peer_id=ne_disconnect_info.peer_id(+)ne_statewherene_state.peer_id=dcc_ne_log.peer_id(+)该SQL三句复杂冗k,从执行并划可看曲亥语句执行了多次的表扫描和复杂表连接，读者应该可以看生，当前主要的优化方案在于如何等价改写SQL以减少表扫描次数。首先想到本案例中为构造由err_cnt,recv_cnt,recv_byte,send_cnt,send

9、_byte这5个字段而完成的一5个SQL结果集能否简化。由于这些字段都来自同一张dcc_ne_log表，仅是条件不同而已，由此才考虑用CASE语句改造代码，将原来5个结果集的写法改造成1个结果集的写法，这样dcc_ne_log这个大表扫描次数就由5次缩减为1次了。经适当.换，这构造5个字段的代码可改写如下：selectpeer_id,COUNT(CASEWHENRESULT<>1THEN1END)err_cnt,COUNT(CASEWHENdirection='recv'THEN1END)recv_cnt,SUM(CASEWHENdirection='rec

10、v'THENlengthEND)recv_byte,COUNT(CASEWHENdirection='send'THEN1END)send_cnt,SUM(CASEWHENdirection='send'THENlengthEND)send_bytefromdcc_ne_logwherelog_time>=trunc(sysdate)GROUPBYpeer_id进一步分析如下ne_disconnect_info结果集的写法：selectdistinctdsl1.peer_idpeer_id,nvl(ne_disconnect_info.ne_sta

11、te,1)ne_statefromdcc_sys_logdsl1,(selectdistinctdnl.peer_idpeer_id,decode(action,'disconnect',0,'connect',0,1)ne_statefromdcc_sys_logdsl,dcc_ne_logdnlwheredsl.peer_id=dnl.peer_idand(dsl.action='disconnect'anddsl.cause='关闭端)or(dsl.action='connect'anddsl.cause='

12、连接主机失败')andlog_type='对端交互'anddsl.log_time=(selectmax(log_time)fromdcc_sys_logwherepeer_id=dnl.peer_idandlog_type='对端交互')ne_disconnect_infowheredsl1.peer_id=ne_disconnect_info.peer_id(+)为了清晰的将结而展现6或，将上述两次改造前脚本用WITH子句分别封装为ne_state和dcc_ne_log_time,最终整体SQL语句改写完毕后，代码量大大减少的同时性能极大的提升了，表

13、扫描次数从总计7次缩减为2次，执行完成时间从原先的300秒缩短为5秒。完整改造后的最终SQL代码优雅精致，具体如下：withne_stateas(SELECTa.peer_id,CASEWHENdnl.peer_idISNOTNULLANDstrIN('disconnect关闭对端,'connect连接主机失败)THEN'0'ELSE'1'ENDne_stateFROM(SELECTpeer_id,MIN(action|cause)KEEP(DENSE_RANKLASTORDERBYlog_time)strFROMdcc_sys_logdslWH

14、ERElog_type='对端交互'GROUPBYpeer_id)a,(SELECTDISTINCTpeer_idFROMdcc_ne_log)dnlWHEREa.peer_id=dnl.peer_id(+),dcc_ne_log_timeas(selectpeer_id,COUNT(CASEWHENRESULT<>1THEN1END)err_cnt,COUNT(CASEWHENdirection='recv'THEN1END)recv_cnt,SUM(CASEWHENdirection='recv'THENlengthEND)rec

15、v_byte,COUNT(CASEWHENdirection='send'THEN1END)send_cnt,SUM(CASEWHENdirection='send'THENlengthEND)send_bytefromdcc_ne_logwherelog_time>=trunc(sysdate)betweentrunc(sysdate)andsysdateGROUPBYpeer_id)selectdistinctne_state.peer_idpeer_name,to_char(ne_state.ne_state)peer_state,(casewhen

16、ne_state.ne_state=0thento_char(0)elseNVL(select'1'fromdcc_sys_logwherepeer_id=ne_state.peer_idandaction='active'andrownum=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,

17、0)recv_cnt,decode(ne_state.ne_state,0,'0',nvl(dnlt.recv_byte,0)recv_byte,decode(ne_state.ne_state,0,'0',nvl(dnlt.send_cnt,0)send_cnt,decode(ne_state.ne_state,0,'0',nvl(dnlt.send_byte,0)send_bytefromne_state,dcc_ne_log_timednltwherene_state.peer_id=dnlt.peer_id(+)案例2.一注重分布函数减少

18、袤扫描次数，如KEEP和DENSE_RANK的结合这个例系来自案例1中，为了说明技巧，特定再次独立成一小节说明，案例1中的如下部分ne_disconnect_info结果集的写法：selectdistinctdsl1.peer_idpeer_id,nvl(ne_disconnect_info.ne_state,1)ne_statefromdcc_sys_logdsl1,(selectdistinctdnl.peer_idpeer_id,decode(action,'disconnect',0,'connect',0,1)ne_statefromdcc_sys_l

19、ogdsl,dcc_ne_logdnlwheredsl.peer_id=dnl.peer_idand(dsl.action='disconnect'anddsl.cause='关闭端)or(dsl.action='connect'anddsl.cause='连接主机失败')andlog_type='对端交互'anddsl.log_time=(selectmax(log_time)fromdcc_sys_logwherepeer_id=dnl.peer_idandlog_type='对端交互')ne_disc

20、onnect_infowheredsl1.peer_id=ne_disconnect_info.peer_id(+)原先至少需矍扫篇2次！为在根届KEEP结合DENSE_RANK的方式，将表扫描从2次变为了1次，具体代码改写如下：SELECTa.peer_id,CASEWHENdnl.peer_idISNOTNULLANDstrIN('disconnect关闭对端','connect连接主机失败')THEN'0'ELSE'1'ENDne_stateFROM(SELECTpeer_id,MIN(action|cause)KEEP(D

21、ENSE_RANKLASTORDERBYlog_time)strFROMdcc_sys_logdslWHERElog_type='对端交互'GROUPBYpeer_id)a,(SELECTDISTINCTpeer_idFROMdcc_ne_log)dnlWHEREa.peer_id=dnl.peer_id(+)案例3注重GROUPBY(CASEWHEN)之类的合并技巧selectdecode(so.sFileName,'SNP_20','SNP','HNIC_2','HNIC','IBRC_2',&

22、#39;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_','IBRST',so.sFileName)业务名称，so.sFileC

23、ount合并前文件个数，so.sRecordNum合并前总记录数，ta.tFileCount合并后文件个数，ta.tRecordNum合并后总记录数，NVL(so1.sFileCount,0)合并前当天文件个数，NVL(so1.sRecordNum,0)合并前当天文件总记录数，NVL(so2.sFileCount,0)合并前昨天文件个数，NVL(so2.sRecordNum,0)合并前昨天文件总记录数from(selectsubstr(a.file_name,1,6)sfileName,count(*)sFileCount,sum(sRecordNum)sRecordNumfrom(selec

24、tdistinctbsf.file_name,bsf.record_numsRecordNum,bsf.create_timefromBUSINESS_SEND_FILELISTbsf,IDEP_PLUGIN_AUTO_RELATIONiparwherebsf.file_id=ipar.bus_file_idandtrunc(ipar.relation_time)=to_date('2010-08-05','yyyy-mm-dd')agroupbysubstr(a.file_name,1,6)orderbysubstr(a.file_name,1,6)soLEF

25、TJOIN(selectsubstr(a.file_name,1,6)sfileName,count(*)sFileCount,sum(sRecordNum)sRecordNumfrom(selectdistinctbsf.file_name,bsf.record_numsRecordNum,bsf.create_timefromBUSINESS_SEND_FILELISTbsf,IDEP_PLUGIN_AUTO_RELATIONiparwherebsf.file_id=ipar.bus_file_idandtrunc(ipar.relation_time)=to_date('2010

26、-08-05','yyyy-mm-dd')ANDFILE_NAMELIKE'%20100805%')agroupbysubstr(a.file_name,1,6)orderbysubstr(a.file_name,1,6)so1ON(so.sFileName=so1.sFileName)LEFTJOIN(selectsubstr(a.file_name,1,6)sfileName,count(*)sFileCount,sum(sRecordNum)sRecordNumfrom(selectdistinctbsf.file_name,bsf.record_

27、numsRecordNum,bsf.create_timefromBUSINESS_SEND_FILELISTbsf,IDEP_PLUGIN_AUTO_RELATIONiparwherebsf.file_id=ipar.bus_file_idandtrunc(ipar.relation_time)=to_date('2010-08-05','yyyy-mm-dd')ANDFILE_NAMEnotLIKE'%20100805%')agroupbysubstr(a.file_name,1,6)orderbysubstr(a.file_name,1,6

28、)so2ON(so.sFileName=so2.sFileName)LEFTJOIN(selectsubstr(a.file_name,1,6)tFileName,count(*)tFileCount,sum(record_num)tRecordNumfrom(selectdistinctipsf.file_name,ipsf.record_numfromidep_plugin_send_filelistipsfwheretrunc(ipsf.create_time)=to_date('2010-08-05','yyyy-mm-dd')andremark=

29、9;处理成功')agroupbysubstr(a.file_name,1,6)orderbysubstr(a.file_name,1,6)taON(so.sFileName=ta.tFileName)whereso.sFileNamenotlike'MVI%'unionselectso.sFileName,so.sFileCount,(so.sRecordNum-(so.sFileCount*2)sRecordNum,ta.tFileCount,ta.tRecordNum,NVL(so1.sFileCount,0),(nvl(so1.sRecordNum,0)-(nvl

30、(so1.sFileCount,0)*2),NVL(so2.sFileCount,0),(nvl(so2.sRecordNum,0)-(nvl(so2.sFileCount,0)*2)from(selectsubstr(a.file_name,1,3)sfileName,count(*)sFileCount,sum(sRecordNum)sRecordNumfrom(selectdistinctbsf.file_name,bsf.record_numsRecordNum,bsf.create_timefromBUSINESS_SEND_FILELISTbsf,IDEP_PLUGIN_AUTO_

31、RELATIONiparwherebsf.file_id=ipar.bus_file_idandtrunc(ipar.relation_time)=to_date('2010-08-05','yyyy-mm-dd')agroupbysubstr(a.file_name,1,3)orderbysubstr(a.file_name,1,3)soLEFTJOIN(selectsubstr(a.file_name,1,3)sfileName,count(*)sFileCount,sum(sRecordNum)sRecordNumfrom(selectdistinctbs

32、f.file_name,bsf.record_numsRecordNum,bsf.create_timefromBUSINESS_SEND_FILELISTbsf,IDEP_PLUGIN_AUTO_RELATIONiparwherebsf.file_id=ipar.bus_file_idandtrunc(ipar.relation_time)=to_date('2010-08-05','yyyy-mm-dd')ANDFILE_NAMELIKE'MVI100805%')agroupbysubstr(a.file_name,1,3)orderbysu

33、bstr(a.file_name,1,3)so1ON(so.sFileName=so1.sFileName)LEFTJOIN(selectsubstr(a.file_name,1,3)sfileName,count(*)sFileCount,sum(sRecordNum)sRecordNumfrom(selectdistinctbsf.file_name,bsf.record_numsRecordNum,bsf.create_timefromBUSINESS_SEND_FILELISTbsf,IDEP_PLUGIN_AUTO_RELATIONiparwherebsf.file_id=ipar.

34、bus_file_idandtrunc(ipar.relation_time)=to_date('2010-08-05','yyyy-mm-dd')ANDFILE_NAMEnotLIKE'MVI100805%')agroupbysubstr(a.file_name,1,3)orderbysubstr(a.file_name,1,3)so2ON(so.sFileName=so2.sFileName)LEFTJOIN(selectsubstr(a.file_name,1,3)tFileName,count(*)tFileCount,sum(recor

35、d_num)tRecordNumfrom(selectdistinctipsf.file_name,ipsf.record_numfromidep_plugin_send_filelistipsfwheretrunc(ipsf.create_time)=to_date('2010-08-05','yyyy-mm-dd')andremark='处理成功')agroupbysubstr(a.file_name,1,3)orderbysubstr(a.file_name,1,3)taON(so.sFileName=ta.tFileName)WHEREs

36、o.sFileName='MVI'分析1 .将trunc(ipar.relation_time)=to_date('2010-08-05','yyyy-mm-dd')等等类似之处改写为如下，要避免对列进行运算，这样会导致用不上索引，除非是建立了函数索引。ipsf.create_time>=to_date('2010-08-05','yyyy-mm-dd')andipsf.create_time<to_date('2010-08-05','yyyy-mm-dd')+12 .

37、可通过CASEWHEN语句进一步减少表扫描次数，如(count(CASEWHENFILE_NAMELIKE'%20100805%'THEN1END)sFileCount1)，类似如上的修改，可以等价改写，将本应用的表扫描从8次减少为4次。3 .更进一步利用groupbysubstr(a.file_name,1,CASEWHENa.file_namelike'MVI%'THEN3ELSE6END),可将表扫描从4次再次减少为2次最终SQL改写优化为：selectdecode(so.sFileName,'SNP_20','SNP',&

38、#39;HNIC_2','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_

39、','IBRST',4 o.sFileName)业务名称，5 o.sFileCount合并前文件个数，casewhenso.sfilenamelike'MVI%'then(so.sRecordNum-(so.sFileCount*2)elseso.sRecordNumend合并前总记录数，ta.tFileCount合并后文件个数，ta.tRecordNum合并后总记录数，NVL(so.sFileCount1,0)合并前当天文件个数，casewhenso.sfilenamelike'MVI%'then(nvl(so.sRecordNum1,

40、0)-(nvl(so.sFileCount1,0)*2)elseNVL(so.sRecordNum1,0)end合并前当天文件总记录数，NVL(so.sFileCount2,0)合并前昨天文件个数，casewhenso.sfilenamelike'MVI%'then(nvl(so.sRecordNum2,0)-(nvl(so.sFileCount2,0)*2)elseNVL(so.sRecordNum2,0)end合并前昨天文件总记录数from(selectsubstr(a.file_name,1,CASEWHENa.file_namelike'MVI%'THE

41、N3ELSE6END)sfileName,count(*)sFileCount,sum(sRecordNum)sRecordNum,count(CASEWHEN(FILE_NAMELIKE'%20100805%'ANDFILE_NAMEnotlike'MVI%')OR(FILE_NAMELIKE'MVI100805%'ANDFILE_NAMElike'MVI%')THEN1END)sFileCount1,sum(CASEWHEN(FILE_NAMELIKE'%20100805%'ANDFILE_NAMEnotlik

42、e'MVI%')OR(FILE_NAMELIKE'MVI100805%'ANDFILE_NAMElike'MVI%')THENsRecordNumEND)sRecordNum1,count(CASEWHEN(FILE_NAMENOTLIKE'%20100805%'ANDFILE_NAMEnotlike'MVI%')OR(FILE_NAMENOTLIKE'MVI100805%'ANDFILE_NAMElike'MVI%')THEN1END)sFileCount2,sum(CASEWHE

43、N(FILE_NAMENOTLIKE'%20100805%'ANDFILE_NAMEnotlike'MVI%')OR(FILE_NAMENOTLIKE'MVI100805%'ANDFILE_NAMElike'MVI%')THENsRecordNumEND)sRecordNum2from(selectdistinctbsf.file_name,bsf.record_numsRecordNum,bsf.create_timefromBUSINESS_SEND_FILELISTbsf,IDEP_PLUGIN_AUTO_RELATIONi

44、parwherebsf.file_id=ipar.bus_file_idandipar.relation_time>=to_date('2010-08-05','yyyy-mm-dd')andipar.relation_time<to_date('2010-08-05','yyyy-mm-dd')+1)agroupbysubstr(a.file_name,1,CASEWHENa.file_namelike'MVI%'THEN3ELSE6END)orderbysubstr(a.file_name,1,CA

45、SEWHENa.file_namelike'MVI%'THEN3ELSE6END)soLEFTJOIN(selectsubstr(a.file_name,1,CASEWHENa.file_namelike'MVI%'THEN3ELSE6END)tFileName,count(*)tFileCount,sum(record_num)tRecordNumfrom(selectdistinctipsf.file_name,ipsf.record_numfromidep_plugin_send_filelistipsfwhereipsf.create_time>=

46、to_date('2010-08-05','yyyy-mm-dd')andipsf.create_time<to_date('2010-08-05','yyyy-mm-dd')+1andremark='处理成功')agroupbysubstr(a.file_name,1,CASEWHENa.file_namelike'MVI%'THEN3ELSE6END)orderbysubstr(a.file_name,1,CASEWHENa.file_namelike'MVI%'THEN3

47、ELSE6END)taON(so.sFileName=ta.tFileName)案例4寻找用or替代unionall的时机selectpeer_id对端标识,null源域名，null目标域名，alert_type告警类型，log_time告警时间,cause告警内容，deal_log处理状态，deal_staff处理人，deal_time处理时间，remark备注fromdcc_sys_logwhereaction='disconnect'andcauselike对端被关闭%'anddeal_log='deal_log'andalert_type=

48、9;alert_type'andlog_time>=TO_DATE('2010-08-02','YYYY-MM-DD')andlog_time<TO_DATE('2010-08-03','YYYY-MM-DD')+1union(selectpeer_id对端标识,origin_host源域名,dest_host目标域名，alert_type告警类型，log_time告警时间,cause告警内容，deal_log处理状态，deal_staff处理人，deal_time处理时间，remark备注fromdcc_ne

49、_logwhereresult=0andcauselike'parser失败'anddeal_log='deal_log'andalert_type='alert_type'andlog_time>=TO_DATE('2010-08-02','YYYY-MM-DD')andlog_time<TO_DATE('2010-08-03','YYYY-MM-DD')+1)union(selectpeer_id对端标识,origin_host源域名,dest_host目标域名，al

50、ert_type告警类型，log_time告警时间,cause告警内容，deal_log处理状态，deal_staff处理人，deal_time处理时间，remark备注fromdcc_ne_logwhereresult_code='DIAMETER_UNABLE_TO_DELIVER'andsvcctx_idlike'SR-Timeout%'anddeal_log='deal_log'andalert_type='alert_type'andlog_time>=TO_DATE('2010-08-02',&#

51、39;YYYY-MM-DD')andlog_time<TO_DATE('2010-08-03','YYYY-MM-DD')+1)union(selectpeer_id对端标识,null源域名，null目标域名，alert_type告警类型，log_time告警时间,cause告警内容，deal_log处理状态，deal_staff处理人，deal_time处理时间，remark备注fromdcc_sys_logwhereaction='disconnect'andcauselike'接收消息异常%'anddeal_l

52、og='deal_log'andalert_type='alert_type'andlog_time>=TO_DATE('2010-08-02','YYYY-MM-DD')andlog_time<TO_DATE('2010-08-03','YYYY-MM-DD')+1)很明显，此处的UNIONALL完全可以用OR来改造，等价改写，减少表扫描次数。优化改造后SQL为：selectpeer_id对端标识,null源域名，null目标域名，alert_type告警类型，log_time告警时间

53、,cause告警内容，deal_log处理状态，deal_staff处理人，deal_time处理时间，remark备注fromdcc_sys_logwhereaction='disconnect'and(causelike'对端被关闭%'orcauselike'接收消息异常%')anddeal_log='deal_log'andalert_type='alert_type'andlog_time>=TO_DATE('2010-08-02','YYYY-MM-DD')andlo

54、g_time<TO_DATE('2010-08-03','YYYY-MM-DD')+1unionselectpeer_id对端标识,origin_host源域名,dest_host目标域名，alert_type告警类型，log_time告警时间,cause告警内容，deal_log处理状态，deal_staff处理人，deal_time处理时间，remark备注fromdcc_ne_logwhere(result=0andcauselike'parser失败%')or(result_code='DIAMETER_UNABLE_TO_

55、DELIVER'andsvcctx_idlike'SR-Timeout%')anddeal_log='deal_log'andalert_type='alert_type'andlog_time>=TO_DATE('2010-08-02','YYYY-MM-DD')andlog_time<TO_DATE('2010-08-03','YYYY-MM-DD')+1二.精兵简政解释为：明确原始需求，去除多余，少走弯路。案例5行列转换此处是必须的吗？withdcc_sys

56、_log_tmpas(select*fromdcc_sys_logwherepeer_id='SR2001.ChinaT'andlog_time>=TO_DATE('2010-05-02','YYYY-MM-DD')andlog_time<TO_DATE('2010-08-03','YYYY-MM-DD')+1),dcc_ne_log_tmpas(select*fromdcc_ne_logwherepeer_id='SC2.ChinaT'andlog_time>=TO_DATE(&

57、#39;2010-05-02','YYYY-MM-DD')andlog_time<TO_DATE('2010-08-03','YYYY-MM-DD')+1),AAS(selectpeer_id,sum(dk_cnt)dk_cnt,sum(send_normal)send_normal,sum(recv_normal)recv_normal,sum(recv_timeout)recv_timeout,sum(send_timeout)send_timeout,sum(exception_cnt)exception_cnt,sum(un

58、recvcca_cnt)unrecvcca_cnt,sum(unsendcca_cnt)unsendcca_cntfrom(selectpeer_id,casewhentype='断开次数'thencntelse0enddk_cnt,casewhentype='发送正常包个数'thencntelse0endsend_normal,casewhentype='接受正常包个数'thencntelse0endrecv_normal,casewhentype='等待CCA应答超时包个数'thencntelse0endrecv_timeou

59、t,casewhentype='消息包发送超时个数'thencntelse0endsend_timeout,casewhentype='异常包的个数thencntelse0endexception_cnt,casewhentype='接收CCA不可达包个数,thencntelse0endunrecvcca_cnt,casewhentype='发送CCA不可达包个数'thencntelse0endunsendcca_cntfrom(selectpeer_id,断开次数'type,nvl(count(1),0)cntfromdcc_sys_l

60、og_tmpwhereaction='disconnect'andcauselike对端被关闭%'groupbypeer_idunionallselectpeer_id,'发送正常由数,type,nvl(count(1),0)fromdcc_ne_log_tmpwhereresult=1anddirection='send'groupbypeer_idunionallselectpeer_id,'接受正常包个数'type,nvl(count(1),0)fromdcc_ne_log_tmpwhereresult=1anddirect

61、ion='recv'groupbypeer_idunionallselectpeer_id,'等待CCA应答超时包个数'type,nvl(count(1),0)fromdcc_ne_log_tmpwheresvcctx_idlike'SR-Timeout%'groupbypeer_idunionallselectpeer_id,'消息包发送超时个数'type,nvl(count(1),0)fromdcc_sys_log_tmpwherecause='发送消息包超时'groupbypeer_idunionallsel

62、ectpeer_id,'异常包的个数'type,nvl(count(1),0)fromdcc_ne_log_tmpwhereresult=0groupbypeer_idunionallselectpeer_id,'接收CCA不可达包个数'type,nvl(count(1),0)fromdcc_ne_log_tmpwhereresult=1anddirection='recv'andresult_code='DIAMETER_UNABLE_TO_DELIVER'groupbypeer_idunionallselectpeer_id,

63、'发送CCA不可达包个数type,nvl(count(1),0)fromdcc_ne_log_tmpwhereresult=1anddirection='send'andresult_code='DIAMETER_UNABLE_TO_DELIVER'groupbypeer_id)groupbypeer_id)selectpeer_id网元名称,nvl(dk_cnt,0)断开次数，nvl(send_normal,0)发送正常包个数，nvl(recv_normal,0)接受正常包个数，nvl(recv_timeout,0)等待CCA应答超时包个数，nvl(s

64、end_timeout,0)消息包发送超时个数，nvl(exception_cnt,0)异常包的个数，nvl(unrecvcca_cnt,0)接收CCA不可达包个数，nvl(unsendcca_cnt,0)发送CCA不可达包个数from(selecta.*fromA,dcc_ne_infobwherea.peer_id(+)=b.peer_idunionallselecta.*fromA,dcc_ne_infobwherea.peer_id=b.peer_id(+)andb.peer_idisnull).此处其实是行列转换的需求，但是实际上根本无需行列转换，开发人员先将数据UNIONNALL的方式进行竖表方式插入，然后再行列转换车那个横表，完全多余，直接用CA