关于Oracle数据库管理系统的分页查询

分页查询SQL:
select * from ( select t.*,rownum RN  from tableName t) where RN between 1 and 5;

 

select * from (select a.*,rownum row_num from (select * from mytable t order by t.id desc) a) b 
where b.row_num between 1 and 10

 

a.

SELECT * FROM ( SELECT A.*, ROWNUM RN FROM (SELECT * FROM TABLE_NAME) A WHERE ROWNUM <= 40)WHERE RN >= 21

b.

SELECT * FROM ( SELECT A.*, ROWNUM RN FROM (SELECT * FROM TABLE_NAME) A ) WHERE RN BETWEEN 21 AND 40

 

对比这两种写法，绝大多数的情况下，第一个查询的效率比第二个高得多。