在Oracle数据库中限制查询返回的行数，主要有两种常见方式：传统的ROWNUM伪列和12c之后引入的FETCH FIRST或OFFSET FETCH标准语法。两者都能实现“只拿前N条”的效果，但在排序、分页以及执行计划层面存在明显差异。如果不清楚它们的底层机制，很容易写出看似正确却返回错误数据的SQL。
一、ROWNUM伪列的工作原理
ROWNUM是Oracle在读取到一行数据并放入结果集时，按顺序分配的一个从1开始的伪列。它并不是数据表中真实存在的字段，而是在查询执行过程中动态生成的。关键点在于：ROWNUM的分配发生在WHERE条件过滤之后、ORDER BY排序之前（对于未使用内联视图的简单查询而言）。这意味着如果直接写WHERE ROWNUM > 5 ORDER BY id，永远拿不到数据，因为ROWNUM为1的行被过滤后，后续行会重新编号为1，导致条件始终不成立。
因此，使用ROWNUM做分页或限制行数时，必须先排序再套一层子查询，让排序后的结果作为内联视图，然后外层用ROWNUM限制。例如要取按工资降序的前十名员工，正确写法如下：
SELECT *
FROM (
SELECT empno, ename, sal
FROM emp
ORDER BY sal DESC
)
WHERE ROWNUM <= 10;
上述代码中，内层子查询先完成排序，外层对排序后的结果集依次赋予ROWNUM并保留前10行。如果要做分页，比如取第11到20行，需要两层嵌套：
SELECT *
FROM (
SELECT a.*, ROWNUM rn
FROM (
SELECT empno, ename, sal
FROM emp
ORDER BY sal DESC
) a
WHERE ROWNUM <= 20
)
WHERE rn > 10;
这种写法的优势是兼容所有Oracle版本，从9i到19c都支持。缺点是嵌套层级多，SQL可读性较差，并且在复杂分析场景下优化器有时无法将谓词推入内层，导致性能不如新语法。
二、FETCH FIRST与OFFSET FETCH标准语法
从Oracle 12c开始，数据库支持了ANSI SQL标准的行限制语法，即FETCH FIRST n ROWS ONLY以及OFFSET m ROWS FETCH NEXT n ROWS ONLY。这种语法由优化器原生处理，语义直观，不需要借助伪列和子查询嵌套。
基本用法非常简单，直接在ORDER BY之后写上限制子句即可：
SELECT empno, ename, sal FROM emp ORDER BY sal DESC FETCH FIRST 10 ROWS ONLY;
如果需要分页，比如跳过前10行再取10行，可以写成：
SELECT empno, ename, sal FROM emp ORDER BY sal DESC OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
使用FETCH语法的好处是代码清晰，不容易写出逻辑错误的分页SQL。同时优化器能够更准确地估算基数，生成更优的执行计划。不过它要求数据库版本在12c及以上，对于仍运行在11g及更早版本的环境无法使用。
三、两种方式的对比与选型建议
从功能上看，ROWNUM和FETCH都能完成限制行数的任务，但在实际工程中要考虑版本兼容与可维护性。下面的表格列出了主要差异：
| 对比维度 | ROWNUM方式 | FETCH语法 |
|---|---|---|
| 最低版本 | 所有版本 | Oracle 12c及以上 |
| 语法复杂度 | 需嵌套子查询 | 单行附加子句 |
| 排序正确性 | 须手动先排序再过滤 | 随ORDER BY自然生效 |
| 执行计划可控性 | 依赖写法，可能阻碍谓词推入 | 优化器原生支持，较优 |
在新建项目中，若数据库版本确定不低于12c，优先使用FETCH FIRST或OFFSET FETCH，既减少出错概率，也方便后续迁移到其他支持标准语法的数据库。维护老系统或必须兼容低版本时，则继续使用ROWNUM嵌套写法，并注意内层排序不可省略。
还有一种常见误区是认为ROWNUM可以和ORDER BY写在同一层并直接限制。如前文所述，在未嵌套的情况下ROWNUM在排序前分配，可能导致返回的不是排序后的前N行。通过理解伪列生成时机，可以避免这类隐蔽错误。
四、结合性能与可读性的实践要点
当表数据量很大时，无论哪种方式都应确保ORDER BY的列有索引支撑，否则数据库需要做全表排序，代价很高。对于ROWNUM写法，若内层使用索引有序扫描，外层限制可以提前终止全表扫描，效率提升明显。
在编写持久层代码如MyBatis或JDBC时，建议将行数限制逻辑封装在SQL内部，而不是先查出全量结果再用代码截取。这样能大幅降低网络传输与内存消耗。示例中使用FETCH语法的Mapper片段如下：
<select id="selectTopEmployees" resultType="Employee">
SELECT empno, ename, sal
FROM emp
ORDER BY sal DESC
OFFSET #{offset} ROWS
FETCH NEXT #{limit} ROWS ONLY
</select>
总体来看，限制返回行数虽是小功能，却直接关系到查询正确性与系统性能。根据运行环境选择合适语法，并理解其执行机制，是每位涉及Oracle开发的人员应具备的基础能力。