ORACLE-ROWNUM及分页(总结)

1.ROWNUM的理解

对于 Oracle的 rownum问题，很多资料都说不支持>,>=,=,between...and，只能用以上符号(<、<=、!=)，并非说用>,>=,=,between..and时会提示SQL语法错误，而是经常是查不出一条记录来，还会出现似乎是莫名其妙的结果来，其实您只要理解好了这个 rownum伪列的意义就不应该感到惊奇，同样是伪列，rownum与 rowid可有些不一样，下面以例子说明。

1）select t.*,rownumfrom base_student t whererownum<20此SQL当然是没有任何问题的。但，这里得注意此处查询出来的结果是按照数据库记录插入顺序给排序出的20条记录。

2）select t.*,rownumfrom base_student t whererownum >20此SQL就有问题了。在此我说下rownum，rownum是从数据库中（当然根据你的查询条件）取出记录后根据插入数据顺序从1开始顺序递增。而当取出第一条数据的时候rownum为1，条件>20不满足，则不取出，如此循环，再次取出又rownum为1，判断不满足，所以得不到数据。

3）select t.*,rownumfrom base_student t whererownum >0或者

   select t.*,rownumfrom base_student twhererownum >=1当然这也是可行的。因为此SQL满足上面2）的规则rownum从1开始。

4）select t.*,rownumfrom base_student t whererownumbetween10and20这句SQL是不行的，根据2)rownum规则不满足。

   select t.*,rownumfrom base_student twhererownumbetween1and10这句SQL可行，查询出根据数据插入数据顺序前10条数据。

2.ROWNUM的运用分页

首先贴出根据rownum分页的标准版SQL，再进行分析。

select * from(select t.*,rownum rn from(select *from base_studentorderbynodesc) twhererownum<10000)where rn>9980;

分析：

1）图中1区域SQL:select *from base_studentorderbynodesc此为你所需要查询数据SQL语句。

2）图中2区域SQL:select t.*,rownum rn from (

                      select *from base_studentorderbynodesc

                      )t

              whererownum <10000

根据你的查询SQL查出rumnum<10000的数据。

Ø  在此你是否想问，为什么不直接用：

select t.* from base_student twhererownum <10000

在此我明确的告诉你，用此SQL不是不行，是可行的。但是这句SQL是根据数据库记录插入顺序查出记录自动编号1-10000。理想是美好的，但是现实是残酷的。我们现实使用的时候通常有自己的排序顺序。

Ø  在此你是否还想问，可以不可以用：

select *from (

    select t.*,rownumas rownum_from base_student torderbyno

) where rownum_<10000

在此再次强调下rownum的含义，内部嵌套select t.*,rownumas rownum_ from base_student torderbyno中的rownum生成顺序：先根据数据库数据插入顺序查询出数据生成rownum，后才order by操作，所以你会发现你想要的排序顺序分页，数据并不合你意，就是因为顺序问题。

Ø  而图中2区域SQL，可以理解为：先根据内部嵌套SQL的排序顺序生成临时的一个表，而此表格的数据顺序正是你所需要的，在此用rownum<10000就行了。

Ø  在此我也曾由此疑问：

select t.*,rownum rnfrom (

     select *from base_studentorderbynodesc

 ) t whererownumbetween9980and10000

在此我根据rownum定义（上面ROWNUM的理解2）可查看）得知，rownum是从1开始查询编号的，所以这条SQL根本不能查出数据。

3）所以我们引出了第三次嵌套，用第二层嵌套的rownum作为此嵌套插叙数据的一个字段，在第三次的时候筛选。

3.其他分页方法

1）ROWID

执行时间 0.094s

2）ROWNUM

执行时间 0.202s

3)分析函数row_number()

执行时间 0.249s

l  总结：

ROWID最快，因为rowid为表中唯一记录，在内部嵌套查询只查询它，所以效率最高。但是通用性不强。

ROWNUM速度次之，因为嵌套查询数据。但是只要传入自身所需SQL就可以实现分页。

分析函数ROW_NUMBER()最差，虽然分析函数很好用（个人也经常用），但是效率真的不敢恭维。（此处执行和rownum相差不大，但是实际多实践证明相差非常大）

4.结语

That’s all.在此声明，此为个人理解，若发现问题，忘指出共同探讨。共同进步。

@ORACLE