Oracle分页查询语句举例分析

这篇文章主要介绍“Oracle分页查询语句举例分析”，在日常操作中，相信很多人在Oracle分页查询语句举例分析问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”Oracle分页查询语句举例分析”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！

ORDER BY STOPKEY和ORDER BY在翻页查询的最后几页的性能差异：

SQL> CREATE TABLE T AS SELECT A.* FROM DBA_OBJECTS A, DBA_USERS B, TAB;

表已创建。

SQL> SELECT COUNT(*) FROM T;

 COUNT(*)
----------
   458064

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T')

PL/SQL 过程已成功完成。

SQL> SET AUTOT TRACE
SQL> SET TIMING ON
SQL> SELECT OBJECT_ID, OBJECT_NAME
 2  FROM
 3   (
 4    SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME
 5    FROM
 6     (
 7      SELECT OBJECT_ID, OBJECT_NAME FROM T ORDER BY TIMESTAMP
 8     )
 9    WHERE ROWNUM <= 20
10   )
11  WHERE RN >= 11;

已选择10行。

已用时间:  00: 00: 00.03

Execution Plan
----------------------------------------------------------
  0      SELECT STATEMENT Optimizer=CHOOSE (Cost=13888 Card=20 Bytes=1840)
  1    0   VIEW (Cost=13888 Card=20 Bytes=1840)
  2    1     COUNT (STOPKEY)
  3    2       VIEW (Cost=13888 Card=458064 Bytes=36187056)
  4    3         SORT (ORDER BY STOPKEY) (Cost=13888 Card=458064 Bytes=18780624)
  5    4           TABLE ACCESS (FULL) OF 'T' (Cost=537 Card=458064 Bytes=18780624)

Statistics
----------------------------------------------------------
         0  recursive calls
         0  db block gets
      5579  consistent gets
         0  physical reads
         0  redo size
       694  bytes sent via SQL*Net to client
       503  bytes received via SQL*Net from client
         2  SQL*Net roundtrips to/from client
         1  sorts (memory)
         0  sorts (disk)
        10  rows processed

SQL> SELECT OBJECT_ID, OBJECT_NAME
 2  FROM
 3   (
 4    SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME
 5    FROM
 6     (
 7      SELECT OBJECT_ID, OBJECT_NAME FROM T ORDER BY TIMESTAMP
 8     )
 9   )
10  WHERE RN BETWEEN 11 AND 20;

已选择10行。

已用时间:  00: 00: 09.05

Execution Plan
----------------------------------------------------------
  0      SELECT STATEMENT Optimizer=CHOOSE (Cost=13888 Card=458064 Bytes=42141888)
  1    0   VIEW (Cost=13888 Card=458064 Bytes=42141888)
  2    1     COUNT
  3    2       VIEW (Cost=13888 Card=458064 Bytes=36187056)
  4    3         SORT (ORDER BY) (Cost=13888 Card=458064 Bytes=18780624)
  5    4           TABLE ACCESS (FULL) OF 'T' (Cost=537 Card=458064 Bytes=18780624)

Statistics
----------------------------------------------------------
         0  recursive calls
        41  db block gets
      5579  consistent gets
      7935  physical reads
         0  redo size
       689  bytes sent via SQL*Net to client
       503  bytes received via SQL*Net from client
         2  SQL*Net roundtrips to/from client
         0  sorts (memory)
         1  sorts (disk)
        10  rows processed

对于翻页查询的前几页，采用ORDER BY STOPKEY的方式比ORDER BY性能上有很大的优势，那么对于分页查询的最后几页，ORDER BY STOPKEY是否和其他分页查询技术一样，性能比普通方式还要低很多：

SQL> SELECT OBJECT_ID, OBJECT_NAME
 2  FROM
 3   (
 4    SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME
 5    FROM
 6     (
 7      SELECT OBJECT_ID, OBJECT_NAME FROM T ORDER BY TIMESTAMP
 8     )
 9    WHERE ROWNUM <= 458060
10   )
11  WHERE RN >= 458051;

已选择10行。

已用时间:  00: 00: 09.07

Execution Plan
----------------------------------------------------------
  0      SELECT STATEMENT Optimizer=CHOOSE (Cost=13888 Card=458060 Bytes=42141520)
  1    0   VIEW (Cost=13888 Card=458060 Bytes=42141520)
  2    1     COUNT (STOPKEY)
  3    2       VIEW (Cost=13888 Card=458064 Bytes=36187056)
  4    3         SORT (ORDER BY STOPKEY) (Cost=13888 Card=458064 Bytes=18780624)
  5    4           TABLE ACCESS (FULL) OF 'T' (Cost=537 Card=458064 Bytes=18780624)

Statistics
----------------------------------------------------------
         0  recursive calls
        41  db block gets
      5579  consistent gets
      7933  physical reads
         0  redo size
       667  bytes sent via SQL*Net to client
       503  bytes received via SQL*Net from client
         2  SQL*Net roundtrips to/from client
         0  sorts (memory)
         1  sorts (disk)
        10  rows processed

SQL> SELECT OBJECT_ID, OBJECT_NAME
 2  FROM
 3   (
 4    SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME
 5    FROM
 6     (
 7      SELECT OBJECT_ID, OBJECT_NAME FROM T ORDER BY TIMESTAMP
 8     )
 9   )
10  WHERE RN BETWEEN 458051 AND 458060;

已选择10行。

已用时间:  00: 00: 10.01

Execution Plan
----------------------------------------------------------
  0      SELECT STATEMENT Optimizer=CHOOSE (Cost=13888 Card=458064 Bytes=42141888)
  1    0   VIEW (Cost=13888 Card=458064 Bytes=42141888)
  2    1     COUNT
  3    2       VIEW (Cost=13888 Card=458064 Bytes=36187056)
  4    3         SORT (ORDER BY) (Cost=13888 Card=458064 Bytes=18780624)
  5    4           TABLE ACCESS (FULL) OF 'T' (Cost=537 Card=458064 Bytes=18780624)

Statistics
----------------------------------------------------------
         0  recursive calls
        41  db block gets
      5579  consistent gets
      7935  physical reads
         0  redo size
       649  bytes sent via SQL*Net to client
       503  bytes received via SQL*Net from client
         2  SQL*Net roundtrips to/from client
         0  sorts (memory)
         1  sorts (disk)
        10  rows processed

出乎意料的是，虽然ORDER BY STOPKEY的方式在分页查询的最后几页性能也有明显的下降，但是在和普通的ORDER BY相比，无论从逻辑读、物理读还是从执行时间上看，二者都属于一个数量级上的。

看来ORDER BY STOPKEY排序方式，在STOPKEY接近排序总量的时候也不会有明显的性能下降。

到此，关于“Oracle分页查询语句举例分析”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注亿速云网站，小编会继续努力为大家带来更多实用的文章！