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接近排序总量之早晚啊无谋面暴发举世瞩目的性降低。

相关文章