Oracle - ROWNUM、BETWEEN...AND... 分页查询

测试表 EMP

SELECT * FROM EMP;

 

ROWNUM（顺便对比下 ROWID）

ROWID 和 ROWNUM 都是伪列，但含义完全不同。

• ROWID 是物理地址，用于定位 Oracle 中具体数据的物理存储位置，用于定位数据表中某条数据的位置，是唯一的、也不会改变。
• ROWNUM 则是 SQL 的输出结果排序。表示查询某条记录在整个结果集中的位置， 同一条记录，查询条件不同，对应的 ROWNUM 是不同的，而 ROWID 是不会变的。
• ROWID 是相对不变的，ROWNUM 会变化，尤其是使用 ORDER BY 的时候。

ROWNUM 对性能的影响：

• ROWNUM 可以避免 Oracle 在磁盘上进行排序。ROWNUM 无法避免全表扫描的发生，但是它可以避免对整个表数据的排序操作，在指定了 ROWNUM 后，排序操作在内存中可以轻松完成。

 

WHERE ROWNUM >= 1、ROWNUM >= 0、ROWNUM > 0，查询结果一致

SELECT * FROM EMP WHERE ROWNUM >= 1;
SELECT * FROM EMP WHERE ROWNUM >= 0;
SELECT * FROM EMP WHERE ROWNUM > 0;

 

WHERE ROWNUM = 1，查询结果为一条记录，没毛病

SELECT * FROM EMP WHERE ROWNUM = 1;

 

WHERE ROWNUM > 1 或者 ROWNUM = 一个比 1 大的数（ROWNUM = 2），查询结果为空

SELECT * FROM EMP WHERE ROWNUM > 1;-- 查询的不是第 2~14 条记录，查询结果为空！ 
SELECT * FROM EMP WHERE ROWNUM = 2;-- 以为查询的结果是第 2 条记录吗？不好意思，查询结果为空！
SELECT * FROM EMP WHERE ROWNUM >= 10;-- 是不是以为查询的结果是表中第 10~14 条记录，但是明显不是，查询结果为空！

 

上面几组 SQL，执行结果，解析如下：

• ROWNUM > 0、ROWNUM >= 0、ROWNUM >=1，如果 WHERE ROWNUM 条件如前面三种的话，查询的结果为 ROWNUM 从 1 开始，到最后一条记录（即全部记录）
• 原因是：ROWNUM 作为 Oracle 表记录的伪列，从 1 开始，即ROWNUM（1、2、3、4、5...、14），ROWNUM > 1，对于第一行来说，并不是真值，因为 ROWNUM 从 1 开始，ROWNUM > 1，真值为 false，而后表记录中原 ROWNUM 为 2 的记录补上，新的 ROWNUM 为 1~13，同理，ROWNUM > 1 真值还是 false，然后初始记录 ROWNUM = 3 的记录补上，新的 ROWNUM 为 1~12，以此类推，ROWNUM > 1 结果真值永远为 false，所以，查询的结果集总为空！
• 结果可知，SELECT * FROM EMP WHERE ROWNUM [condition]，condition 条件永远不要使用 ROWNUM > ? 或者 ROWNUM = n（n!=1），因为这样意义不大。

 

WHERE ROWNUM < 或 <= n（n 大于 1）

SELECT * FROM EMP WHERE ROWNUM <= 10;-- 前 10 条记录

解析：

• SELECT * FROM EMP WHERE ROWNUM <= 10，结果和预期一致。
• ROWNUM <= 10，第一条记录 ROWNUM 为 1，条件 ROWNUM <=10 为 true，然后 ROWNUM 增长为 2，条件仍为 true，直到 ROWNUM 增长为 10，条件仍为 true，ROWNUM 增长为 11，ROWNUM <= 10 条件为 false，此时原记录中 ROWNUM = 12 的记录补上，ROWNUM 仍为 11，ROWNUM <= 10 条件恒为 false。则查询结果集是：ROWNUM 从 1 到 10 的记录

 

WHERE ROWNUM != n（n 大于 1）

SELECT * FROM EMP WHERE ROWNUM != 10;

分析过程，和上面类似。ROWNUM 从 1~9，ROWNUM != 10 结果都为 ture，当 ROWNUM 增长到 10，ROWNUM != 10，判断条件为 false，所以只查询出第 1~9 条记录。

 

BETWEEN...AND...

SELECT * FROM EMP WHERE ROWNUM BETWEEN 1 AND 10;

 

BETWEEN 2 AND n，从 2 或者 任何大于 1  开始，到 n

SELECT * FROM EMP WHERE ROWNUM BETWEEN 2 AND 10;-- 查询为空！

 分析：

• BETWEEN START AND END，ROWNUM 总是从 1 开始，所以 START 去和数据文件或者缓冲区中读取读取第一条记录（ROWNUM 为 1），不符合，所以 ROWNUM = 1 这条记录从临时表中删除，下一条记录补上，作为新的记录 ROWNUM  =1 继续比较 ，真值仍为 false，以此类推，导致查询结果集为空！
• 所以，想直接通过 WHERE ROWNUM BETWEEN START AND END 来进行分页查询，不奏效。为此就要借助子查询，将 ROWNUM 伪列，作为字段，在子查询中查询出来，然后做分页查询。

 

Oracle 利用 ROWNUM 做分页查询：

SELECT M.*
  FROM (SELECT ROWNUM AS RN, T.* FROM EMP T WHERE ROWNUM <= 8) M
 WHERE RN >= 3;

SELECT M.*
  FROM (SELECT ROWNUM AS RN, T.* FROM EMP T) M
 WHERE RN BETWEEN 3 AND 8;