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分页查询语句举例分析”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注创新互联网站，小编会继续努力为大家带来更多实用的文章！
网站栏目：Oracle分页查询语句举例分析
网站链接：http://azwzsj.com/article/pschoc.html
