oracle分页为什么要给rownum列取别名

通常的oracle分页为三层查询：

SELECT * 
FROM 
 (
    SELECT A.*, ROWNUM RN 
    FROM 
     (
        SELECT * 
        FROM 表名（如：测试表）
     ) A 
    WHERE ROWNUM <= 上限（如：20）
 )
WHERE RN > 下限（如：10）

1.第一层查需要的数据。

2.第二层控制数据上限。

3.第三层控制数据下限。

其中，第二层为rownum<=上限，第三层为rn(rownum别名列)>下限。

 

（1）为什么要给rownum列取别名？或者问为什么不合并二三层查询？

为了提前生成rownum的值。具体的说，是为了避开 rownum>下限 这种语句，这种语句干扰了rownum值的生成。

 

（2）是什么问题？

rownum列在where中直接 rownum>下限，基本会出错（查不到数据）。

而rownum<=上限则基本不会出错，并且会被oracle的优化器把这个条件推到内层。

 

（3）为什么rownum>下限会出错？

首先，rownum列是在取数据的时候，分配值。

关于值的生成过程，一个不恰当的说法：（注：下面的rownum值称为序号，序号从1开始递增分配）

1.取出一条数据，给它分配一个序号。

2.判断where条件是否满足。

3.不满足，回收或者说复用现在的序号。

       特点是第三步回收了序号，在这种情况下，如果第二步的where条件就是因为序号才不满足，会怎么样？

接下来的判断也会错，因为用的是上次那个序号值，再接下来也是。

 

如果说where条件是rownum>5 ：

1.取出一条数据，给它分配1

2.判断where条件：rownum（1）> 5

3.不满足，回收或者说复用现在的序号。下次还分配1，还得不满足。

因此所有数据都不会满足条件，得到0条数据。

       而如果是rownum<6，在预期的前5条数据中不会触发第2、3步的死循环，得到预期数据。至于从第6条开始的死循环，不会影响我们要的结果。

 

       使用rownum的别名列时，一般是在外层查询中使用，这时rownum已经分配完了，就不会有问题了。

 

 

最后，直接使用rownum来判断时，对rownum列注意两点：

（1） 一定要有一个1

rownum<上限的时候，rownum是从1开始的，有1；rownum>下限的时候，rownum不一定是从1开始的，大于0或负数还好，有1，大于正整数就没1，结果就会出错。

（2）涉及rownum的条件会影响rownum的生成，如：where mod(rownum,2) = 1

 

另，生成rownum值后，才会进行order by

优先级：分配rownum   >   order by

也就是说用了order by，会发现rownum的顺序不对。所以，分页的时候把rownum<上限放在第二层，就是为了最内层可以先order by数据。