关于Oracle分页

Connected to Oracle9i Enterprise Edition Release 9.2.0.6.0
Connected as dev

SQL>
SQL> SET timing ON;
SQL> CREATE TABLE test_table AS
  2    SELECT rownum x
  3      FROM dual
  4     START WITH rownum = 1
  5    CONNECT BY rownum <= 1000000;

Table created

Executed in 4.125 seconds

SQL> --89991至90000行
SQL> --A方法
SQL> SELECT *
  2    FROM (SELECT /*+ first_rows */
  3           x, rownum num
  4            FROM (SELECT x FROM test_table ORDER BY x DESC)
  5           WHERE rownum <= 90000)
  6   WHERE num >= 89991;

         X        NUM
---------- ----------
    910010      89991
    910009      89992
    910008      89993
    910007      89994
    910006      89995
    910005      89996
    910004      89997
    910003      89998
    910002      89999
    910001      90000

10 rows selected

Executed in 4.579 seconds

SQL> --B方法
SQL> SELECT *
  2    FROM (SELECT x, rownum num FROM (SELECT x FROM test_table ORDER BY x DESC))
  3   WHERE num BETWEEN 89991 AND 90000;

         X        NUM
---------- ----------
    910010      89991
    910009      89992
    910008      89993
    910007      89994
    910006      89995
    910005      89996
    910004      89997
    910003      89998
    910002      89999
    910001      90000

10 rows selected

Executed in 6.891 seconds

SQL> --1至10行
SQL> --A方法
SQL> SELECT *
  2    FROM (SELECT /*+ first_rows */
  3           x, rownum num
  4            FROM (SELECT x FROM test_table ORDER BY x DESC)
  5           WHERE rownum <= 10)
  6   WHERE num >= 1;

         X        NUM
---------- ----------
   1000000          1
    999999          2
    999998          3
    999997          4
    999996          5
    999995          6
    999994          7
    999993          8
    999992          9
    999991         10

10 rows selected

Executed in 1.844 seconds

SQL> --B方法
SQL> SELECT *
  2    FROM (SELECT x, rownum num FROM (SELECT x FROM test_table ORDER BY x DESC))
  3   WHERE num BETWEEN 1 AND 10;

         X        NUM
---------- ----------
   1000000          1
    999999          2
    999998          3
    999997          4
    999996          5
    999995          6
    999994          7
    999993          8
    999992          9
    999991         10

10 rows selected

Executed in 6.906 seconds

SQL> --999991至1000000行
SQL> --A方法
SQL> SELECT *
  2    FROM (SELECT /*+ first_rows */
  3           x, rownum num
  4            FROM (SELECT x FROM test_table ORDER BY x DESC)
  5           WHERE rownum <= 1000000)
  6   WHERE num >= 999991;

         X        NUM
---------- ----------
        10     999991
         9     999992
         8     999993
         7     999994
         6     999995
         5     999996
         4     999997
         3     999998
         2     999999
         1    1000000

10 rows selected

Executed in 6.703 seconds

SQL> --B方法
SQL> SELECT *
  2    FROM (SELECT x, rownum num FROM (SELECT x FROM test_table ORDER BY x DESC))
  3   WHERE num BETWEEN 999991 AND 1000000;

         X        NUM
---------- ----------
        10     999991
         9     999992
         8     999993
         7     999994
         6     999995
         5     999996
         4     999997
         3     999998
         2     999999
         1    1000000

10 rows selected

Executed in 6.469 seconds

SQL> DROP TABLE test_table;

Table dropped

Executed in 0.125 seconds

SQL> --我们可以看到，A方法优于B方法，
SQL> --特别是当分页查询的页数靠前的时候，速度差别很明显，
SQL> --只是分页在最后几页的时候，2种方法消耗的时间才差不多。