oracle Hash join耗时太久，怎么优化？

Question

除了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

Answer 1

建立索引簇试试