往期回顾：

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

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

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

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

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

在数据库查询优化中，分页查询是最常见的性能瓶颈之一。看似相同的逻辑实现，却可能在执行计划上产生天壤之别的性能差异。本文作为《左右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 <= 10;
SELECT * FROM table(dbms_xplan.display_cursor(null,null,'allstats last'));

执行计划分析：

| Id | Operation           | Name | Starts | E-Rows | A-Rows | A-Time      | Buffers | Reads  |
|----|---------------------|------|--------|--------|--------|-------------|---------|--------|
|  0 | SELECT STATEMENT    |      |      1 |        |     10 | 00:00:00.13 |    1048 |    713 |
|  1 |  VIEW               |      |      1 |  91662 |     10 | 00:00:00.13 |    1048 |    713 |
|  2 |   COUNT             |      |      1 |        |  73118 | 00:00:00.10 |    1048 |    713 |
|  3 |    TABLE ACCESS FULL| T    |      1 |  91662 |  73118 | 00:00:00.09 |    1048 |    713 |

性能问题剖析：

 1. 全表扫描代价：处理了73,118行数据，仅为获取10行结果

 2. 资源浪费严重：消耗1048个缓冲区，执行时间0.13秒

 3. COUNT操作低效：普通COUNT操作无法提前终止

 4. 内存占用过大：所有行都需要分配ROWNUM后才能过滤

高效分页写法：COUNT STOPKEY的优势

语句2：内层限制的分页查询

select * from (select t.*, rownum as rn from t where rownum <= 10) a where a.rn >= 1;
SELECT * FROM table(dbms_xplan.display_cursor(null,null,'allstats last'));

执行计划分析：

| Id | Operation           | Name | Starts | E-Rows | A-Rows | A-Time      | Buffers |
|----|---------------------|------|--------|--------|--------|-------------|---------|
|  0 | SELECT STATEMENT    |      |      1 |        |     10 | 00:00:00.01 |       5 |
|  1 |  VIEW               |      |      1 |     10 |     10 | 00:00:00.01 |       5 |
|  2 |   COUNT STOPKEY     |      |      1 |        |     10 | 00:00:00.01 |       5 |
|  3 |    TABLE ACCESS FULL| T    |      1 |  91662 |     10 | 00:00:00.01 |       5 |

性能优势分析：

 1. COUNT STOPKEY机制：达到指定行数后立即停止扫描

 2. 精确数据处理：仅处理10行数据，完全匹配需求

 3. 资源消耗极低：仅用5个缓冲区，执行时间0.01秒

 4. 内存效率极高：避免了不必要的行处理和内存分配

性能对比深度分析

COUNT STOPKEY机制深度解析

工作原理

COUNT STOPKEY是Oracle优化器的一种智能机制：

 1. 预设阈值：根据ROWNUM条件确定停止点

 2. 逐行监控：在数据扫描过程中实时计数

 3. 即时终止：达到指定行数后立即停止后续操作

 4. 资源释放：及时释放不再需要的系统资源

触发条件

COUNT STOPKEY机制的激活需要满足特定条件：

-- ✅ 能触发STOPKEY的写法
WHERE rownum <= N
WHERE rownum < N+1
-- ❌ 无法触发STOPKEY的写法  
WHERE rownum >= N (起始行限制)
WHERE rownum BETWEEN N AND M (区间限制)

适用场景

 1. TOP-N查询：获取前N条记录

 2. 分页查询首页：展示第一页数据

 3. 数据采样：随机采样指定数量的数据

 4. 快速预览：查看表结构和数据样例

分页查询最佳实践

标准分页模板

-- 获取第21-30条记录的标准写法

SELECT * FROM (
    SELECT a.*, ROWNUM rn FROM (
        SELECT * FROM table_name 
        ORDER BY column_name  -- 确保结果稳定性
    ) a WHERE ROWNUM <= 30
) WHERE rn >= 21;

性能优化要点

 - 内层ROWNUM限制：在最内层子查询中设置上限

 - 排序字段索引：为ORDER BY字段建立合适索引

 - 分页大小控制：避免单页数据量过大

 - 缓存策略配合：结合应用层缓存机制

 - 大数据量分页优化

对于千万级数据表的深度分页，推荐使用游标分页：

-- 基于主键的游标分页（性能更优）
SELECT * FROM table_name 
WHERE id > :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 <= 10 ORDER BY column_name;
-- ✅ 正确：先排序后分配ROWNUM
SELECT * FROM (
    SELECT * FROM table_name ORDER BY column_name
) WHERE rownum <= 10;

实际应用场景

1. 报表系统分页

在BI报表系统中，用户经常需要分页浏览大量数据：

-- 高效的报表分页查询
SELECT * FROM (
    SELECT a.*, ROWNUM rn FROM (
        SELECT employee_id, name, salary, department
        FROM employees 
        ORDER BY salary DESC
    ) a WHERE ROWNUM <= :page_size * :page_num
) WHERE rn > :page_size * (:page_num - 1);

2. API接口分页

RESTful API经常需要支持分页参数：

-- 支持offset和limit的分页实现
SELECT * FROM (
    SELECT a.*, ROWNUM rn FROM (
        SELECT * FROM products 
        WHERE status = 'ACTIVE'
        ORDER BY created_date DESC
    ) a WHERE ROWNUM <= :offset + :limit
) WHERE rn > :offset;

总结

ROWNUM分页优化的核心在于理解和善用COUNT STOPKEY机制。通过将ROWNUM限制条件放在内层子查询中，可以让Oracle优化器在达到指定行数后立即停止数据扫描，从而实现数量级的性能提升。

这种优化技巧不仅显著提高了查询效率，还大幅降低了数据库负载，特别适用于大数据量场景下的分页查询。在实际应用中，配合合理的索引设计和缓存策略，可以构建出高性能、高并发的分页查询系统。

记住：在Oracle分页查询中，执行计划中出现COUNT STOPKEY是性能优化成功的重要标志。掌握这一技巧，将让你的SQL查询性能获得质的飞跃。

未完待续..

左右SQL执行计划妙招 ⑦——执行计划SQL写法差异改变【rownum实体化】

公众号：收获不止数据库

微信：ljbyxl1213

• “大白话人工智能” 系列

• “数据库拍案惊奇” 系列

• “世事洞明皆学问” 系列