当前位置: 代码迷 >> Oracle开发 >> 请各位大侠帮小弟我看看这个SQL可以优化吗?现在performance很低,多谢了
  详细解决方案

请各位大侠帮小弟我看看这个SQL可以优化吗?现在performance很低,多谢了

热度:40   发布时间:2016-04-24 07:23:08.0
请各位大侠帮我看看这个SQL可以优化吗?现在performance很低,谢谢了。
如下 这个SQL,结果已经认为是正确的,就是performance比较低,
解释一下:
前面是一些属性(类似数据仓库中dimension),后面是一些Amount(类似数据仓库中fact),是一个字段A5.AM,在A5.AMTYNA的不同情况下取值,请问大侠们有什么好的建议吗?数据库已经成型,只能从现有的设计上想方法了。
附explain plan。


SELECT  
TO_CHAR(SYSDATE,'YYYYMMDD') AS AS_OF_DT,
'SG' AS COUNTRY_CD,
I3.RIENALID AS LCIN,
I1.RIENSHNA AS GCIN,
I1.RIENNA AS CUST_NAME,
A3.QPRTYCD AS TOP_LMT_PRD_TYPE,
A3.PRCD AS TOP_LMT_PRD_CD,
A4.PRTYDE AS PRD_DESC,
A3.STDA AS FAC_START_DT,
A3.EDDA AS FAC_END_DT,
A5.RITYNA AS RISK_TYPE,
'SGD' AS BASE_CURR_CD,
A5.CUCD AS ACCT_CURR_CD,
SUM(DISTINCT(DECODE(A5.AMTYNA,'APP_FAC_LMT',A5.AM,0))) AS APPR_LMT_ACTUAL,
SUM(DISTINCT(DECODE(A5.AMTYNA,'APP_FAC_LMT',A5.AM_BASE,0))) AS APPR_LMT_BASE,
SUM(DISTINCT(DECODE(A5.AMTYNA,'APP_FAC_LMT',A5.AM_BASE,0))) AS APPR_LMT_SGD,
SUM(DISTINCT(DECODE(A5.AMTYNA,'ACT_FAC_LMT',A5.AM,0))) AS ACT_LMT_ACTUAL,
SUM(DISTINCT(DECODE(A5.AMTYNA,'ACT_FAC_LMT',A5.AM_BASE,0))) AS ACT_LMT_BASE,
SUM(DISTINCT(DECODE(A5.AMTYNA,'ACT_FAC_LMT',A5.AM_BASE,0))) AS ACT_LMT_SGD,
SUM(DISTINCT(DECODE(A5.AMTYNA,'APP_IND_UTIL',A5.AM,0))) AS OUTSTD_AMT_ACTUAL,
SUM(DISTINCT(DECODE(A5.AMTYNA,'APP_IND_UTIL',A5.AM_BASE,0))) AS OUTSTD_AMT_BASE,
SUM(DISTINCT(DECODE(A5.AMTYNA,'APP_IND_UTIL',A5.AM_BASE,0))) AS OUTSTD_AMT_SGD,
SUM(DISTINCT(DECODE(A5.AMTYNA,'APP_IND_UND',A5.AM,0))) AS UNDR_CMMT_ACTUAL,
SUM(DISTINCT(DECODE(A5.AMTYNA,'APP_IND_UND',A5.AM_BASE,0))) AS UNDR_CMMT_BASE,
SUM(DISTINCT(DECODE(A5.AMTYNA,'APP_IND_UND',A5.AM_BASE,0))) AS UNDR_CMMT_SGD
FROM PLP_TSRC_ACR_REPORT_DATES A1
INNER JOIN PLP_TSRC_ACR_FAC_HIER A2 ON TO_CHAR(A2.TIMEIDDATE,'DD-MM-YYYY') = TO_CHAR(A1.CURRTIMEID,'DD-MM-YYYY')
  AND A2.AGGR5BAAPID='ACA'
INNER JOIN PLP_TSRC_ACR_PROD_T A3 ON A2.TIMEID=A3.TIMEID AND A2.AGGR5PRCD=A3.PRCD
INNER JOIN PLP_TSRC_IC_MPT_T A4 ON A4.QPRTYCD=A3.QPRTYCD AND A4.TOTIMEID = 99991231
INNER JOIN PLP_TSRC_ACR_TLEB_FCRS_T A5 ON A5.TIMEID=A2.TIMEID AND A2.AGGR5PRCD=A5.FACPRCD 
  AND A5.RITYNA IN ('LLE','PCE') AND A5.CMFL = 'N'
INNER JOIN PLP_TSRC_IC_REX_T I1 ON I1.RIENSHNA=A5.RRRIENSHNA AND I1.TOTIMEID=99991231
INNER JOIN PLP_TSRC_IC_REUD_T I2 ON I2.RIENGEID=I1.RIENGEID AND I2.UDTYNA = 'Data Source System'
  AND I2.TOTIMEID=99991231 AND I2.UDDA = 'C'
INNER JOIN PLP_TSRC_IC_REAL_T I3 ON I3.RIENGEID=I1.RIENGEID AND A2.TIMEID=I3.TIMEID
  AND I3.ALTYNA = 'SG_CIN_NO'
GROUP BY I3.RIENALID,
  I1.RIENSHNA,
  I1.RIENNA,
  A3.QPRTYCD,
  A3.PRCD,
  A4.PRTYDE,
  相关解决方案