oracle rownum

最新推荐文章于 2024-03-26 09:30:52 发布

转载最新推荐文章于 2024-03-26 09:30:52 发布 · 592 阅读

文章标签：

#oracle #rownum #赱向远方

数据库专栏收录该内容

32 篇文章

订阅专栏

本文解析了 Oracle 数据库中 ROWNUM 伪列的工作原理，包括其在不同情况下的表现，例如排序列有索引与无索引时的情况。同时介绍了如何利用 ROWNUM 进行分页查询，并给出了使用分析函数 row_number 的替代方案。

先看以下两条语句的执行结果：
语句一：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,empno,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,t.* from
(select empno,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,empno,sal from emp)
where num between 6 and 10;