Oracle分页技术

1、使用两层嵌套

SELECT *FROM (SELECT A.*, ROWNUM RNFROM (SELECT * FROM edw_t100_bal_all) AWHERE ROWNUM <= 40)WHERE RN >= 21;

2、使用between..and..

SELECT *FROM (SELECT A.*, ROWNUM RN FROM (SELECT * FROM edw_t100_bal_all) A)WHERE RN between 21 and 40;

3、实践证明：方法1比方法2效率更高

SELECT /* + FIRST_ROWS */ *FROM (SELECT A.*, ROWNUM RNFROM (SELECT * FROM edw_t100_bal_all) AWHERE ROWNUM <= 40)WHERE RN >= 21;

以上语句会以更高的效率执行。