# Oracle ROWNUM用法和分页查询总结

Oracle ROWNUM用法和分页查询总结 (opens new window)

# ROWNUM

可能都知道ROWNUM只适用于小于或小于等于，如果进行等于判断，那么只能等于1，不能进行大于的比较。 ROWNUM是oracle系统顺序分配为从查询返回的行的编号，返回的第一行分配的是1，第二行是2，依此类推。 ROWNUM总是从1开始，不管当前的记录是否满足查询结果，ROWNUM返回的值都是1，如果这条记录的值最终满足所有的条件，那么ROWNUM会递加，下一条记录的ROWNUM会返回2，否则下一条记录的ROWNUM仍然返回1。理解了这一点，就清楚为什么一般的ROWNUM大于某个值或等于某个不为1的值是无法返回结果的，因此对于每条记录的ROWNUM都是1，而ROWNUM为1不满足查询的结果，所以下一条记录的ROWNUM不会递增，仍然是1，因此所有的记录都不满足条件。

# 分页查询方法

# 1.between

查询的最外层控制分页的最小值和最大值

SELECT * FROM   
(  
SELECT A.*, ROWNUM RN   
FROM (SELECT * FROM TABLE_NAME) A   
)  
WHERE RN BETWEEN 1 AND 10

1
2
3
4
5
6

# 2. <= >=

SELECT * FROM   
(  
SELECT A.*, ROWNUM RN   
FROM (SELECT * FROM TABLE_NAME) A   
WHERE ROWNUM <= 10  
)  
WHERE RN >= 1

1
2
3
4
5
6
7

# 3.HINT

考虑到多表联合的情况，如果不介意在系统中使用HINT(优化器)的话，可以将分页的查询语句改写为：

SELECT /*+ FIRST_ROWS */ * FROM   
(  
SELECT A.*, ROWNUM RN   
FROM (SELECT * FROM TABLE_NAME) A   
WHERE ROWNUM <= 40  
)  
WHERE RN >= 21

1
2
3
4
5
6
7

效率：绝大多数的情况下，第2个查询的效率比第1个高得多

# 排序列不唯一所带来的问题

如果用来排序的列不唯一，也就是存在值相等的行，可能会造成第一次在前10条返回记录中，某行数据出现了，而第二次在11到第20条记录中，某行数据又出现了。一条数据重复出现两次，就必然意味着有数据在两次查询中都不会出现。其实造成这个问题的原因很简单，是由于排序列不唯一造成的。Oracle这里使用的排序算法不具有稳定性，也就是说，对于键值相等的数据，这种算法完成排序后，不保证这些键值相等的数据保持排序前的顺序。解决这个问题其实也很简单。有两种方法可以考虑。 1）在使用不唯一的字段排序时，后面跟一个唯一的字段。一般在排序字段后面跟一个主键就可以了，如果表不存在主键，跟*ROWID也可以。这种方法最简单，且对性能的影响最小。 2）另一种方法就是使用前面给出过多次的BETWEEN AND的方法。这种方式由于采用表数据的全排序，每次只取全排序中的某一部分数据，因此不会出现上面提到的重复数据问题。但是正是由于使用了全排序，而且*ROWNUM信息无法推到查询内部，导致这种写法的执行效率很低

← oracle连接数配置 oracle连接数配置 →