中国RS项目sql优化：
原始sql如下：
SELECT MAX(MD.Y) Y,MAX(MD.IMONTH) IMONTH,MAX(MD.IYMD) IYMD,MAX(MD.SVRTYPE) SVRTYPE,MAX(MD.SPISUNCM) SPISUNCM ,MAX(table2.table2_NAME) table2_NAME, SUM(ICOUNT) ICOUNT,SUM(RSUCC) RSUCC,SUM(RFAIL1) RFAIL1,SUM(RFAIL2) RFAIL2,SUM(RNRET) RNRET ,SUM(LD.PHONENUM) PHONENUM FROM MT_table1 MD LEFT JOIN LF_MT_table1 LD ON MD.ID=LD.ID LEFT JOIN LF_table2MANAGER table2 ON MD.SVRTYPE=table2.table2_CODE WHERE MD.IYMD>=19991001 AND MD.IYMD<=19991031 and MD.SPISUNCM in (0,1,21) GROUP BY table2.table2_NAME ORDER BY RSUCC DESC,table2.table2_NAME DESC
执行时间45秒，经过诊断，MT_table1表套用全部常量筛选后的数据量为78万行左右，数据库HJ_BUF_SIZE 参数设置为200M，在sql运行过程中对hj buffer使用情况进行跟踪：
Select * from v$sysstat where name like ‘%hj%’
发现sql执行过程中会出现hash分片上temp tablespace的现象，因此考虑使用nest loop代替hash join
SELECT /+ enable_hash_join(0)/
MAX(MD.Y) Y ,
MAX(MD.IMONTH) IMONTH ,
MAX(MD.IYMD) IYMD ,
MAX(MD.SVRTYPE) SVRTYPE ,
MAX(MD.SPISUNCM) SPISUNCM ,
MAX(table2.table2_NAME) table2_NAME,
SUM(ICOUNT) ICOUNT ,
SUM(RSUCC) RSUCC ,
SUM(RFAIL1) RFAIL1 ,
SUM(RFAIL2) RFAIL2 ,
SUM(RNRET) RNRET ,
SUM(LD.PHONENUM) PHONENUM
FROM
MT_table1 MD
LEFT JOIN LF_MT_table1 LD
ON
MD.ID=LD.ID
LEFT JOIN LF_table2MANAGER table2
ON
MD.SVRTYPE=table2.table2_CODE
WHERE
MD.IYMD >=19991001
AND MD.IYMD <=19991031
and MD.SPISUNCM in (0, 1, 21)
GROUP BY
table2.table2_NAME
ORDER BY
RSUCC DESC,
table2.table2_NAME DESC;

优化后执行时间下降到6秒。
社区地址：https://eco.dameng.com