Oracle查询优化求助：3500万级表多子查询慢查询问题

你的原查询最大的问题是重复多次连接PG_PAGO和PG_DETALLE_PAGO——每个统计子查询都会重新执行一次表连接，对于3500万条记录的大表来说，这会带来巨量的重复IO和计算开销，是性能慢的核心原因。下面是针对性的优化方案：

优化后的查询语句 #

通过单次表连接+条件聚合的方式，把所有统计逻辑合并到一次扫描中，避免重复连接：

SELECT 
    FN_FORMAT_PEROPE(R.PEROPE) AS PERIODO,
    NVL(AGG.NRO_EMITIDOS, 0) AS NRO_EMITIDOS,
    NVL(AGG.MONTO_EMITIDO, 0) AS MONTO_EMITIDO,
    NVL(AGG.NRO_ABONADOS, 0) AS NRO_ABONADOS,
    NVL(AGG.MONTO_ABONADO, 0) AS MONTO_ABONADO,
    NVL(AGG.NRO_RECHAZADOS, 0) AS NRO_RECHAZADOS,
    NVL(AGG.MONTO_RECHAZADO, 0) AS MONTO_RECHAZADO,
    NVL(AGG.NRO_INDEBIDOS, 0) AS NRO_INDEBIDOS,
    NVL(AGG.MONTO_INDEBIDO, 0) AS MONTO_INDEBIDO,
    NVL(AGG.NRO_RECUPEROS, 0) AS NRO_RECUPEROS,
    NVL(AGG.MONTO_RECUPERO, 0) AS MONTO_RECUPERO
FROM PG_RESOLUCIONES R
LEFT JOIN (
    SELECT 
        P.PEROPE,
        -- 统计ESTADO=1的记录数和金额
        COUNT(CASE WHEN DP.ESTADO = 1 THEN 1 END) AS NRO_EMITIDOS,
        SUM(CASE WHEN DP.ESTADO = 1 THEN DP.MONTO END) AS MONTO_EMITIDO,
        -- 统计ESTADO=2的记录数和金额
        COUNT(CASE WHEN DP.ESTADO = 2 THEN 1 END) AS NRO_ABONADOS,
        SUM(CASE WHEN DP.ESTADO = 2 THEN DP.MONTO END) AS MONTO_ABONADO,
        -- 统计ESTADO=3的记录数和金额
        COUNT(CASE WHEN DP.ESTADO = 3 THEN 1 END) AS NRO_RECHAZADOS,
        SUM(CASE WHEN DP.ESTADO = 3 THEN DP.MONTO END) AS MONTO_RECHAZADO,
        -- 统计ESTADO=4的记录数和金额
        COUNT(CASE WHEN DP.ESTADO = 4 THEN 1 END) AS NRO_INDEBIDOS,
        SUM(CASE WHEN DP.ESTADO = 4 THEN DP.MONTO END) AS MONTO_INDEBIDO,
        -- 统计PG_PAGO表ESTADO=5的记录数（注意原逻辑是直接查PG_PAGO）
        COUNT(DISTINCT CASE WHEN P.ESTADO = 5 THEN P.IDPAGO END) AS NRO_RECUPEROS,
        -- 统计PG_DETALLE_PAGO表ESTADO=5的金额
        SUM(CASE WHEN DP.ESTADO = 5 THEN DP.MONTO END) AS MONTO_RECUPERO
    FROM PG_PAGO P
    INNER JOIN PG_DETALLE_PAGO DP ON P.IDPAGO = DP.IDPAGO
    GROUP BY P.PEROPE
) AGG ON R.PEROPE = AGG.PEROPE
ORDER BY R.PEROPE ASC;

关键优化点说明 #

• 减少表连接次数：原查询执行了10次子查询，每次都要连接两张大表；优化后只需要连接一次，然后在聚合时完成所有统计，IO开销直接降到原来的1/10左右。
• 条件聚合替代子查询：用CASE WHEN配合COUNT/SUM实现按状态分组统计，逻辑和原查询完全一致，但效率提升显著。
• 处理NRO_RECUPEROS的特殊逻辑：原查询中这个统计是直接查询PG_PAGO的ESTADO=5，所以用COUNT(DISTINCT CASE WHEN P.ESTADO =5 THEN P.IDPAGO END)避免重复计数（因为一个IDPAGO可能对应多条DP记录）。

索引优化建议（针对大表） #

虽然你已经建了索引，但需要确保索引是覆盖索引，避免回表查询：

• 给PG_PAGO创建复合索引：CREATE INDEX IDX_PG_PAGO_PEROPE_ESTADO ON PG_PAGO(PEROPE, ESTADO, IDPAGO);
  包含PEROPE（分组字段）、ESTADO（过滤字段）、IDPAGO（连接字段），可以直接从索引获取所需数据，无需回表。
• 给PG_DETALLE_PAGO创建复合索引：CREATE INDEX IDX_PG_DETALLE_PAGO_IDPAGO_ESTADO_MONTO ON PG_DETALLE_PAGO(IDPAGO, ESTADO, MONTO);
  包含IDPAGO（连接字段）、ESTADO（过滤字段）、MONTO（聚合字段），同样是覆盖索引，避免回表。

额外性能优化建议 #

如果PEROPE是时间周期（比如年月），可以考虑：

• 分区表：对PG_PAGO和PG_DETALLE_PAGO按PEROPE做范围分区，查询时只会扫描对应分区的数据，大幅减少扫描范围。
• 统计信息更新：执行DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => '你的用户名', TABNAME => 'PG_PAGO', CASCADE => TRUE);和DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => '你的用户名', TABNAME => 'PG_DETALLE_PAGO', CASCADE => TRUE);，确保Oracle优化器能生成最优执行计划。

内容的提问来源于stack exchange，提问作者aamapanqui