背景与问题 #

我需要从Oracle数据库提取数据生成HTML表格矩阵，但原始查询涉及多视图关联、GROUP BY操作，运行速度极慢。为优化性能，我决定实现分页，参考相关思路采用了三层嵌套ROWNUM的分页写法，还添加了/*+ FIRST_ROWS(n) */优化提示，可分页后的执行时间居然和未限制行数时差不多，甚至更长。

我的分页查询示例如下：

SELECT * FROM ( 
    SELECT /*+ FIRST_ROWS(n) */ a.*, ROWNUM rnum FROM ( 
        WITH T1 AS ( SELECT ... FROM VIEW_1 INNER JOIN ... WHERE ... GROUP BY ... ), 
        T2 AS ( SELECT ... FROM VIEW_2 INNER JOIN ... WHERE ... GROUP BY ... ), 
        T3 AS ( SELECT ... FROM VIEW_3 LEFT JOIN ... WHERE ... GROUP BY ... ) 
        SELECT T1.LVL, T1.CRSE, T2.ID, T3.GRADE 
        FROM T1 INNER JOIN T2 ON ... LEFT JOIN T3 ON ... 
        ORDER BY T2.ID DESC, T1.LVL, T1.CRSE
    ) a 
WHERE ROWNUM <= (PAGE * 50)) 
WHERE rnum >= ((PAGE-1) * 50);

注：实际查询复杂度更高，且我没有权限查看所用视图的定义，也无法设置变量。原以为添加ROWNUM限制后执行时间会大幅降低，但完全没达到预期，推测问题可能和查询中的GROUP BY操作有关。

排查过程 #

1. 首先我生成了该查询的EXPLAIN PLAN，但由于视图权限限制，无法深入分析视图内部逻辑，暂时没找到明确瓶颈。
2. 随后我转向检查底层表的索引配置，针对查询中涉及的PS_STDNT_ENRL表，执行了以下语句查看已有的索引信息：

SELECT index_owner, index_name, table_name, column_name, column_position 
FROM DBA_IND_COLUMNS 
WHERE table_name = 'PS_STDNT_ENRL' 
ORDER BY index_owner, table_name, index_name, column_position;

解决方案 #

通过获取的索引信息，我调整了查询的WHERE子句，让它能够命中PS_STDNT_ENRL表上的已有索引。调整完成后，分页查询的速度得到了大幅提升，终于达到了预期的性能优化效果。

内容的提问来源于stack exchange，提问作者Bruno Dávila Assad