[Oracle]关于Oracle分页写法的性能分析及ROWNUM说明

[Oracle]关于Oracle分页写法的性能分析及ROWNUM说明
关于分页写法的性能分析及ROWNUM的补充说明
分页写法
⼀、测试前数据准备
SQL>SELECT COUNT(*) FROM BPM_PROCVAR;
COUNT(*)
----------
2121400
1、分页写法⼀
SELECT*
FROM (SELECT ROWNUM RN, A.*
FROM (SELECT*FROM BPM_PROCVAR ORDER BY VARID) A
WHERE ROWNUM <=40) B
WHERE B.RN >=21;
查询结果：20条记录，查询时间：0.031秒
2、分页写法⼆
SELECT*
FROM (SELECT ROWNUM RN, A.*
FROM (SELECT*FROM BPM_PROCVAR ORDER BY VARID) A) B
WHERE B.RN >=21
AND B.RN <=40;
查询结果：20条记录，执⾏时间：13.224秒
三、分页写法结论及其分析
1、结论：Oracle分页写法必须三层嵌套：写法⼀的执⾏效率最⾼、写法⼆的执⾏效率较差（实际测试结果写法⼀：写法⼆=1:426、不同环境测试结果肯定会有偏差）
2、执⾏效率差异分析
是由于CBO优化模式下，Oracle可以将外层的查询条件推到内层查询中，以提⾼内层查询的执⾏效率。

对于第⼀个查询语句，第⼆层的查询条件WHERE ROWNUM <= 40就
可以被Oracle推⼊到内层查询中，这样Oracle查询的结果⼀旦超过了ROWNUM限制条件，就终⽌查询将结果返回了。

⽽第⼆个查询语句，由于查询条件BETWEEN 21 AND 40是存在于查询的第三层，⽽Oracle⽆法将第三层的查询条件推到最内层（即使推到最内层也没有意义，因为最内层查
询不知道RN代表什么）。

因此，对于第⼆个查询语句，Oracle最内层返回给中间层的是所有满⾜条件的数据，⽽中间层返回给最外层的也是所有数据。

数据的过滤在最外层完
成，显然这个效率要⽐第⼀个查询低得多。

上⾯分析的查询不仅仅是针对单表的简单查询，对于最内层查询是复杂的多表联合查询或最内层查询包含排序的情况⼀样有效。

这⾥就不对包含排序的查询进⾏说明了，下⼀篇⽂章会通过例⼦来详细说明。

下⾯简单讨论⼀下多表联合的情况。

对于最常见的等值表连接查询，CBO⼀般可能会采⽤两种连接
⽅式NESTED LOOP和HASH JOIN（MERGE JOIN效率⽐HASH JOIN效率低，⼀般CBO不会考虑）。

在这⾥，由于使⽤了分页，因此指定了⼀个返回的最⼤记录数，NESTED
LOOP在返回记录数超过最⼤值时可以马上停⽌并将结果返回给中间层，⽽HASH JOIN必须处理完所有结果集（MERGE JOIN也是）。

那么在⼤部分的情况下，对于分页查询选
择NESTED LOOP作为查询的连接⽅法具有较⾼的效率（分页查询的时候绝⼤部分的情况是查询前⼏页的数据，越靠后⾯的页数访问⼏率越⼩）。

四、ROWNUM的补充说明
对于rownum来说它是oracle系统顺序分配为从查询返回的⾏的编号，返回的第⼀⾏分配的是1，第⼆⾏是2，依此类推，这个伪字段可以⽤于限制查询返回的总⾏数，⽽且rownum不能以任何表的名称作为前缀。

举例说明：
例如表：student(学⽣)表，表结构为：
ID char(6) --学号
name VARCHAR2(10) --姓名
create table student (ID char(6), name VARCHAR2(100));
insert into sale values('200001',‘张⼀’);
insert into sale values('200002',‘王⼆’);
insert into sale values('200003',‘李三’);
insert into sale values('200004',‘赵四’);
commit;
(1) rownum 对于等于某值的查询条件
如果希望找到学⽣表中第⼀条学⽣的信息，可以使⽤rownum=1作为条件。

但是想找到学⽣表中第⼆条学⽣的信息，使⽤rownum=2结果查不到数据。

因为rownum都是从1开始，但是1以上的⾃然数在rownum做等于判断是时认为都是false条件，所以⽆SQL>select rownum,id,name from student where rownum=1;（可以⽤在限制返回记录条数的地⽅，保证不出错，如：隐式游标）
SQL>select rownum,id,name from student where rownum=1;
ROWNUM ID NAME
---------- ------ ---------------------------------------------------
1200001张⼀
SQL>select rownum,id,name from student where rownum =2;
ROWNUM ID NAME
---------- ------ ---------------------------------------------------
（2）rownum对于⼤于某值的查询条件
如果想找到从第⼆⾏记录以后的记录，当使⽤rownum>2是查不出记录的，原因是由于rownum是⼀个总是从1开始的伪列，Oracle 认为rownum> n(n>1的⾃然数)这种条件依旧不成⽴，所以查不到记录
SQL>select rownum,id,name from student where rownum >2;
ROWNUM ID NAME
---------- ------ ---------------------------------------------------
那如何才能找到第⼆⾏以后的记录呀。

可以使⽤以下的⼦查询⽅法来解决。

注意⼦查询中的rownum必须要有别名，否则还是不会查出记录来，这是因为rownum不是某个表的列，如果不起别名的话，⽆法知道rownum是⼦查询的列还是主查询的列。

SQL>select*from(select rownum no ,id,name from student) where no>2;
NO ID NAME
---------- ------ ---------------------------------------------------
3200003李三
4200004赵四
SQL>select*from(select rownum,id,name from student)where rownum>2;
ROWNUM ID NAME
---------- ------ ---------------------------------------------------
（3）rownum对于⼩于某值的查询条件
如果想找到第三条记录以前的记录，当使⽤rownum<3是能得到两条记录的。

显然rownum对于rownum<n（(n>1的⾃然数）的条件认为是成⽴的，所以可以找到记录。

SQL>select rownum,id,name from student where rownum <3;
ROWNUM ID NAME
---------- ------ ---------------------------------------------------
1200001张⼀
2200002王⼆
综上⼏种情况，可能有时候需要查询rownum在某区间的数据，那怎么办呀从上可以看出rownum对⼩于某值的查询条件是⼈为true的，rownum对于⼤于某值的查询条件直接认为是false的，但是可以间接的让它转为认为是true的。

那就必须使⽤⼦查询SQL>select*from (select rownum no,id,name from student where rownum<=3 ) where no >=2;
NO ID NAME
---------- ------ ---------------------------------------------------
2200002王⼆
3200003李三
（4）rownum和排序
Oracle中的rownum的是在取数据的时候产⽣的序号，所以想对指定排序的数据去指定的rowmun⾏数据就必须注意了。

SQL>select rownum ,id,name from student order by name;
ROWNUM ID NAME
---------- ------ ---------------------------------------------------
3200003李三
2200002王⼆
1200001张⼀
4200004赵四
可以看出，rownum并不是按照name列来⽣成的序号。

系统是按照记录插⼊时的顺序给记录排的号，rowid也是顺序分配的。

为了解决这个问题，必须使⽤⼦查询SQL>select rownum ,id,name from (select*from student order by name);
ROWNUM ID NAME
---------- ------ ---------------------------------------------------
1200003李三
2200002王⼆
3200001张⼀
4200004赵四
这样就成了按name排序，并且⽤rownum标出正确序号（有⼩到⼤）
笔者在⼯作中有⼀上百万条记录的表，在jsp页⾯中需对该表进⾏分页显⽰，便考虑⽤rownum来作，下⾯是具体⽅法(每页
显⽰20条)：
“select*from tabname where rownum<20order by name" 但却发现oracle却不能按⾃⼰的意愿来执⾏，⽽是先随便
取20条记录，然后再order by，后经咨询oracle,说rownum确实就这样，想⽤的话，只能⽤⼦查询来实现先排序，后
rownum，⽅法如下：
"select*from (select*from tabname order by name) where rownum<20",但这样⼀来，效率会较低很多。

后经笔者试验，只需在order by的字段上加主键或索引即可让oracle先按该字段排序，然后再rownum；⽅法不变：
“select*from tabname where rownum<20order by name"
取得某列中第N⼤的⾏
select column_name from
(select table_name.*,dense_rank() over (order by column desc) rank from table_name)
where rank =&N；
　假如要返回前5条记录：
select*from tablename where rownum<6;(或是rownum <=5或是rownum !=6)
假如要返回第5-9条记录：
select*from tablename
where …
and rownum<10
minus
select*from tablename
where …
and rownum<5
order by name
选出结果后⽤name排序显⽰结果。

(先选再排序)
注意：只能⽤以上符号(<、<=、!=)。

select*from tablename where rownum !=10;返回的是前９条记录。

不能⽤：>,>=,=,Between...and。

由于rownum是⼀个总是从1开始的伪列，Oracle 认为这种条件不成⽴，查不到记录.
另外，这个⽅法更快：
select*from (
select rownum r,a from yourtable
where rownum <=20
order by name )
where r >10
这样取出第11-20条记录!(先选再排序再选)
要先排序再选则须⽤select嵌套：内层排序外层选。

rownum是随着结果集⽣成的，⼀旦⽣成，就不会变化了；同时,⽣成的结果是依次递加的，没有1就永远不会有2!
rownum 是在查询集合产⽣的过程中产⽣的伪列，并且如果where条件中存在 rownum 条件的话，则:
1：假如判定条件是常量，则：
只能 rownum =1, <=⼤于1 的⾃然数，=⼤于1 的数是没有结果的，⼤于⼀个数也是没有结果的
即当出现⼀个 rownum 不满⾜条件的时候则查询结束 this is stop key!
2: 当判定值不是常量的时候
若条件是=var , 则只有当var为1 的时候才满⾜条件，这个时候不存在 stop key ,必须进⾏full scan ,对每个满⾜其他where条件的数据进⾏判定
选出⼀⾏后才能去选rownum=2的⾏……。