Oracle分页查询语句(七)
来源:互联网 发布:淘宝货源 知乎 编辑:程序博客网 时间:2024/05/23 19:00
在文章的最后看一下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分页查询语句
- Oracle分页查询语句
- Oracle分页查询语句
- Oracle分页查询语句
- Oracle分页查询语句
- Oracle分页查询语句
- Oracle分页查询语句
- Oracle分页查询语句
- Oracle分页查询语句
- Oracle分页查询语句
- Oracle分页查询语句
- Oracle分页查询语句
- Oracle分页查询语句
- Oracle分页查询语句
- Oracle分页查询语句
- Oracle分页查询语句
- Oracle分页查询语句
- 文本框同步输入
- Ubuntu瞄准移动操作系统,剑指Android
- Oracle分页查询语句(五)
- 添加事件委托
- Oracle分页查询语句(六)
- Oracle分页查询语句(七)
- 与我相亲的剩女竟然是个小三
- 把握趋势,把握未来 —- 小评《只有偏执狂才能生存》
- (找工作的同学注意了!)签offer和三方的区别和注意
- js原型理解
- 西雅图十大最具潜质初创公司盘点
- 工厂方法(Factory Method) -- (模式之八)
- Eclipse plugin插件安装
- 寻找发帖“水王”--《编程之美》笔记