左右SQL执行计划妙招 ⑥ ——执行计划SQL写法差异改变【rownum分页】

梁敬彬梁敬弘兄弟出品

往期回顾

左右SQL执行计划妙招 ①——子查询的应用范围

左右SQL执行计划妙招 ②——Hint无效原因

左右SQL执行计划妙招 ③——执行计划SQL写法差异改变【with子句】

左右SQL执行计划妙招 ④——执行计划SQL写法差异改变【insert all】

左右SQL执行计划妙招 ⑤——执行计划利用设计特性改变【并行度影响】

在Oracle数据库查询优化中，分页查询是最常见的性能瓶颈之一。看似相同的逻辑实现，却可能在执行计划上产生天壤之别的性能差异。本文作为《左右SQL执行计划妙招》系列第六篇，将深入剖析ROWNUM分页查询的优化策略，揭示COUNT STOPKEY机制的威力。

实验环境搭建

为确保测试结果的准确性和可重现性，我们建立标准化的测试环境：

-- 创建测试表
drop table t;
create table t as select * from dba_objects;
-- 设置显示参数
set linesize 1000
set pagesize 2000
set autotrace off
ALTER SESSION SET statistics_level = all;

低效分页写法：全表扫描的陷阱

语句1：外层过滤的分页查询

select * from (select t.*, rownum as rn from t) a where a.rn >= 1 and a.rn  :last_id 
ORDER BY id 
LIMIT 20;

常见误区与注意事项

误区1：ROWNUM的使用限制

-- ❌ 错误写法：无法获得预期结果
SELECT * FROM table_name WHERE rownum > 10;
-- ✅ 正确写法：需要子查询包装
SELECT * FROM (
    SELECT t.*, rownum rn FROM table_name t
) WHERE rn > 10;

误区2：排序与ROWNUM的关系

-- ❌ 错误：先分配ROWNUM后排序
SELECT * FROM table_name WHERE rownum