Oracle 中的 ROWNUM 理解与应用

ROWNUM 是 Oracle 数据库中特有的一个伪列，它表示查询结果集中每一行的行号。ROWNUM 是按查询结果返回的顺序分配的，它从 1 开始递增，因此可以用来对查询结果进行限制和筛选。尽管 ROWNUM 常用于分页和查询优化，但需要注意它的工作原理以及一些使用上的限制。

ROWNUM 的基本用法

SELECT ROWNUM, column1, column2
FROM table_name;

在这条语句中，ROWNUM 会为查询的每一行分配一个递增的行号。从 ROWNUM = 1 开始，到查询结果的最后一行为止。它通常与 WHERE 子句结合，用于限制返回的记录数。例如，返回前 10 条记录：

SELECT ROWNUM, column1, column2
FROM table_name
WHERE ROWNUM <= 10;

这条语句可以有效地截取查询结果的前十条记录。需要注意的是，ROWNUM 是在查询数据时动态生成的，因此其值只反映当前查询的顺序，而不是真实存储在表中的数据。

例如：

select ROWNUM,D.* 
FROM ARCHIVES_CENTER.ARCHIVES_DOT D
WHERE ROWNUM <= 10;

结果有十条：

ROWNUM 的限制和陷阱

1. ROWNUM 先于 WHERE 子句过滤

Oracle 在处理查询时，ROWNUM 是在选择数据的过程中即时分配的，而不是在所有数据选出后再赋值。因此，在执行带有 ROWNUM 的查询时，Oracle 会先生成 ROWNUM，然后应用 WHERE 子句。这会导致一些常见的陷阱，尤其是在结合条件查询时。

例如，下面这条语句将不会返回任何结果：

SELECT *
FROM table_name
WHERE ROWNUM > 10;

这是因为 Oracle 在数据被选择出来时，首先会为第一行分配 ROWNUM = 1，但一旦 ROWNUM > 10 的条件不满足，Oracle 不会再继续递增 ROWNUM，因此不会返回任何行。

例如如下查询结果：

2. 分页查询问题

在实际项目中，分页查询是一个常见的需求，ROWNUM 可以结合子查询和排序来实现分页功能。例如，假设我们需要返回第 11 到第 20 条记录，可以使用以下查询：

SELECT *
FROM (SELECT ROWNUM r, t.*
      FROM (SELECT * FROM table_name ORDER BY column_name) t
      WHERE ROWNUM <= 20)
WHERE r > 10;

• 内层查询首先对数据按某一列进行排序，并为其分配 ROWNUM。
• 外层查询再对已经分配的 ROWNUM 进行过滤，提取出所需的记录。

这个查询首先返回 ROWNUM 小于等于 20 的前 20 条记录，然后在外层查询中过滤掉 ROWNUM 小于等于 10 的行，从而实现分页。

例如下面查询：

SELECT *
FROM (
    SELECT ROWNUM r, D.*
    FROM (
        SELECT DT.* FROM ARCHIVES_CENTER.ARCHIVES_DOT DT ORDER BY DT.DOT_ID
    ) D
    WHERE ROWNUM <= 20
)
WHERE r > 10;

查询结果如下：

ROWNUM 与 ROW_NUMBER() 的区别

Oracle 自 10g 开始引入了 ROW_NUMBER() 函数，它与 ROWNUM 类似，但功能更强大，且使用更加灵活。ROW_NUMBER() 是分析函数，允许你按特定的列进行排序，并为查询结果集中的每一行分配行号。一个典型的 ROW_NUMBER() 用法是：

SELECT ROW_NUMBER() OVER (ORDER BY column_name) AS row_num, column1, column2
FROM table_name;

与 ROWNUM 不同，ROW_NUMBER() 可以对查询结果根据某些列进行排序，然后再分配行号。它在分页查询、排名计算等场景中比 ROWNUM 更加实用。例如：

SELECT * 
FROM (SELECT ROW_NUMBER() OVER (ORDER BY column_name) AS row_num, t.*
      FROM table_name t)
WHERE row_num BETWEEN 11 AND 20;

例如下面查询：

SELECT *
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY DT.DOT_ID) AS row_num, DT.*
    FROM ARCHIVES_CENTER.ARCHIVES_DOT DT
) 
WHERE row_num BETWEEN 11 AND 20;

结果如下：

ROWNUM 使用的最佳实践

1. 快速截取前 N 条记录：使用 ROWNUM 来快速返回查询结果集的前几条记录，这是它的最典型用途。

SELECT * FROM employees WHERE ROWNUM <= 5;

2. 避免直接使用 ROWNUM > N 的条件：由于 Oracle 在行分配 ROWNUM 时的机制问题，ROWNUM > N 的查询将不会返回任何结果。要进行分页操作，需要使用子查询。

3. 分页查询：通过嵌套查询的方式配合 ROWNUM 实现分页，通常与排序子句结合。

4. 复杂排序和分析场景使用 ROW_NUMBER()：如果你需要按照特定的规则进行排序或者更复杂的行号分配，建议使用 ROW_NUMBER()，它提供了更多的控制和灵活性。

总结

ROWNUM 是 Oracle 数据库中一个方便的工具，能够快速限制查询结果，尤其是在需要返回前 N 条记录时。然而，由于 ROWNUM 的工作机制，直接使用 ROWNUM > N 进行分页是不合适的，正确的分页实现需要通过嵌套查询。此外，对于更复杂的排序和分页需求，ROW_NUMBER() 提供了更强大的功能和灵活性。因此，在实际项目中，开发者需要根据具体需求选择合适的工具。