Oracle分頁技術

1、使用兩層嵌套

SELECT *
  FROM (SELECT A.*, ROWNUM RN
          FROM (SELECT * FROM edw_t100_bal_all) A
         WHERE 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 RN
          FROM (SELECT * FROM edw_t100_bal_all) A
         WHERE ROWNUM <= 40)
 WHERE RN >= 21;

以上語句會以更高的效率執行。