Oracle中两个相似ROWNUM查询执行时间差异大的原因及优化

即使id列有索引，外层的ROWNUM会改变Oracle优化器的执行逻辑：

• 不带ORDER BY的查询：Oracle可以通过INDEX FAST FULL SCAN快速扫描索引（无序但IO效率高），拿到数据后直接应用ROWNUM过滤，只取前N行就停止，不需要处理全表数据。
• 带ORDER BY的查询：ROWNUM的特性是必须先生成完整的结果集才能分配行号，所以Oracle需要先把子查询中所有符合条件的行全部取出，再执行SORT ORDER BY操作排序，之后才能过滤出前N行。哪怕id有索引，优化器可能因为成本估算（比如认为FAST FULL SCAN的IO成本更低）选择先全扫索引再排序，而不是走有序的索引扫描，这在千万级数据量下，排序的开销会急剧放大。

至于单独测试子查询时性能相近，是因为单独执行SELECT ... ORDER BY id时，Oracle可以直接走INDEX FULL SCAN（有序扫描），按id的顺序返回数据，不需要额外排序；但当子查询被ROWNUM包裹后，优化器的执行计划选择逻辑发生了变化，优先考虑了全扫的IO成本，忽略了后续排序的巨大开销。

1. 使用ROW_NUMBER()窗口函数替代ROWNUM嵌套 #

这种写法让优化器可以利用id索引的有序性，边扫描边分配行号，达到指定行数就停止，避免全量排序：

SELECT col1, col2, ...  -- 明确列名替代*，减少不必要的数据读取
FROM (
    SELECT 
        t.col1, t.col2, ...,
        ROW_NUMBER() OVER(ORDER BY t.id) AS rn
    FROM your_table t
    -- 子查询2的过滤条件
)
WHERE rn <= 目标行数;

2. 强制走有序索引扫描 #

用优化器提示指定使用id列的索引进行有序扫描，跳过排序步骤：

SELECT * FROM (
    SELECT /*+ INDEX(t idx_table_id) */ t.*  -- idx_table_id是id列的索引名
    FROM your_table t
    -- 子查询2的过滤条件
    ORDER BY t.id
)
WHERE ROWNUM <= 目标行数;

3. 更新表统计信息 #

过时的统计信息会导致优化器做出错误的执行计划选择，执行以下命令更新：

EXEC DBMS_STATS.GATHER_TABLE_STATS('你的用户名', '目标表名', CASCADE => TRUE);

4. 优化索引结构 #

如果子查询有额外的WHERE过滤条件，可以建立组合索引，包含过滤列和id列，比如：

CREATE INDEX idx_table_filter_id ON your_table(filter_col1, filter_col2, id);

这样优化器可以在扫描索引时同时过滤数据，减少需要处理的行数，进一步提升性能。

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