oracle中的rownum、order by与分页

先看以下两条语句的执行结果：

语句一：select rownum,empno,sal from emp order by empno;

ROWNUM EMPNO SAL
---------- ---------- ----------
1 7369 800
2 7499 1600
3 7521 1250
4 7566 2975
5 7654 1250
6 7698 2850
7 7782 2450
8 7788 3000

　　……

语句二：select rownum,sal from emp order by sal;

ROWNUM EMPNO SAL
---------- ---------- ----------
1 7369 800
12 7900 950
11 7876 1100
3 7521 1250
5 7654 1250
14 7934 1300
10 7844 1500
2 7499 1600

……

同样的两个语句，执行结果中的rownum伪列的值却大相径庭，这是什么原因呢？

其实这都是ORACLE内部的查询优化器和索引搞的鬼！

rownum伪列产生的序号是按照数据被查询出来的顺序添加上去的，第一条是1,第二条是2,依次加1。

当将一条语句交给查询优化器处理时：

如果排序列上有索引，则借助索引去查询数据，这样，读取出来的数据和rownum产生的序号是一种正常的对应关系，如语句一的结果（empno上有主键索引）。

如果排序列上没有索引，则使用全表扫描的方式，依次从表中读取数据，读取完成后，最后进行排序，于是产生了类似语句二的结果（sal列上没有索引）。

正是由于排序列上不一定有索引，所以在ORACLE中使用rownum伪列分页时，需要多加一层查询，以保证rownum序号的连续性。

语句三：select rownum,t.* from
(select empno,sal from emp order by sal) t;

ROWNUM EMPNO SAL
---------- ---------- ----------
1 7369 800
2 7900 950
3 7876 1100
4 7521 1250
5 7654 1250
6 7934 1300
7 7844 1500
8 7499 1600

这个结果还满意吧。。。

分页：在外面再加上一层查询。

select * from
(select rownum num,sal from emp order by sal) t)
where num between 6 and 10;

当然，如果使用分析函数row_number就可以省略一层查询了，代码更简单点：

select * from (select row_number() over (order by sal) num,sal from emp) where num between 6 and 10;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!