shaokel
2021-08-09 11:15
采纳率: 98.6%
浏览 45
已结题

oracle Hash join耗时太久,怎么优化?

除了DM_BSDT,DM_BSDW,其他的表数据量都很大
这些MX.PJJL_ID,PJX_ID,PJJL_ID,CPK.BSDT_DM, CPK.BSDW_DM都有索引

sql
SELECT LISTAGG(MX.XXZT, ',') WITHIN GROUP(ORDER BY PJ.PJJL_ID) AS XXZT,
       LISTAGG(PJX.PJDX_DM, ',') WITHIN GROUP(ORDER BY PJ.PJJL_ID) AS PJDX_DM,
       LISTAGG(PJX.PJZB_DM, ',') WITHIN GROUP(ORDER BY PJ.PJJL_ID) AS PJZB_DM,
       DT.BSDT_MC,
       DT.BSDT_MC_JX,
       PJ.PJJL_ID
  FROM YW_PJMX       MX,
       YW_PJJL       PJ,
       YW_PJX        PJX,
       REF_BSDT_BSDW CPK,
       DM_BSDT       DT,
       DM_BSDW       BSDW
 WHERE PJ.PJJL_ID = MX.PJJL_ID(+)
   AND MX.PJX_ID = PJX.PJX_ID(+)
   AND PJ.DT_DM = CPK.BSDT_DM(+)
   AND CPK.BSDT_DM = DT.BSDT_DM
   AND CPK.BSDW_DM = BSDW.BSDW_DM
   AND PJ.LRRQ >= TO_DATE('2020-11-01', 'yyyy-MM-dd')
   AND PJ.LRRQ < TO_DATE('2020-11-30', 'yyyy-MM-dd') + 1
 GROUP BY PJ.PJJL_ID, DT.BSDT_MC, DT.BSDT_MC_JX

img

  • 好问题 提建议
  • 收藏

1条回答 默认 最新

  • Zht_bs 2021-08-09 14:31
    已采纳

    建立索引簇试试

    已采纳该答案
    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题