Oracle分页查询语句举例分析

16次阅读
没有评论

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

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 分页查询语句举例分析”的学习就结束了,希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习,快去试试吧!若想继续学习更多相关知识,请继续关注丸趣 TV 网站,丸趣 TV 小编会继续努力为大家带来更多实用的文章!