Oracle 18g含MAX与GROUP BY的查询执行过慢 求可行替代方案

性能问题根因分析 #

• 你表的主键是(a_num_ver, id, site_id)联合索引，而原查询GROUP BY的字段为id, site_id, sub_id, ae_no，无法命中现有主键索引的有序特性，查询时需要先全表扫描所有数据，再对分组字段做排序聚合，数据量大时排序开销会非常高
• 如果表数据量超过百万级，全表扫描+临时表排序的组合会导致耗时指数级上升

优化方案1：分析函数替代GROUP BY（无需改索引，逻辑兼容） #

如果不方便新增索引，可以用分析函数替代原生GROUP BY聚合，利用Oracle对分析函数的优化减少排序开销：

SELECT id, site_id, sub_id, ae_no, a_num_ver, aer_ver
FROM (
    SELECT id,
           site_id,
           sub_id,
           ae_no,
           a_num_ver,
           aer_ver,
           -- 如果需要保留a_num_ver、aer_ver并列最大值的所有记录，替换ROW_NUMBER为RANK即可
           ROW_NUMBER() OVER (PARTITION BY id, site_id, sub_id, ae_no ORDER BY a_num_ver DESC, aer_ver DESC) rn
    FROM table_1
) t
WHERE rn = 1;

注意：如果你的业务逻辑是取同一个分组内a_num_ver最大的那条记录对应的aer_ver，而非分组内所有记录的aer_ver最大值，该写法完全匹配需求；如果是要两个字段各自的最大值，和原查询逻辑一致。

优化方案2：新增覆盖索引（性能提升最明显） #

如果允许新增索引，直接建包含分组字段+聚合字段的联合覆盖索引，Oracle可以直接走索引快速全扫描，不需要回表也不需要额外排序：

-- 索引按分组字段在前、聚合字段降序在后的顺序创建
CREATE INDEX idx_table1_group_max ON table_1 (id, site_id, sub_id, ae_no, a_num_ver DESC, aer_ver DESC);

索引创建后原SQL和优化方案1的SQL都可以直接命中该索引，性能可以提升数倍到数十倍不等。

优化方案3：关联子查询（适合数据分布不均场景） #

如果分组后每个组的记录数差异很大，关联子查询的写法可以跳过大量无效数据扫描：

SELECT t1.id,
       t1.site_id,
       t1.sub_id,
       t1.ae_no,
       MAX(t1.a_num_ver) as a_num_ver,
       MAX(t1.aer_ver) as aer_ver
FROM table_1 t1
WHERE t1.a_num_ver = (SELECT MAX(t2.a_num_ver) 
                      FROM table_1 t2 
                      WHERE t2.id = t1.id 
                        AND t2.site_id = t1.site_id
                        AND t2.sub_id = t1.sub_id
                        AND t2.ae_no = t1.ae_no)
GROUP BY t1.id, t1.site_id, t1.sub_id, t1.ae_no;

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