ACCESSOracle分页查询语句(四)

终极的例证表明里循环包含排序的气象:

SQL> CREATE TABLE T AS SELECT * FROM
DBA_OBJECTS;

讲明已创设。

SQL> CREATE INDEX
IND_T_OBJECT_NAME ON T (OBJECT_NAME);

目录已开立。

SQL> ALTER TABLE T MODIFY
OBJECT_NAME NOT NULL;

表达已转移。

SQL> EXEC
DBMS_STATS.GATHER_TABLE_STATS(USER, ‘T’)

PL/SQL 过程就成就。

下举行测试包含排序操作的分页查询。可以概括的以查询分为两种植不同境况,第一种消连串就是找引列,这种可以使用索引读取,第两种消序列没有索引。

第一种植情况以足以细分为:完全摸引围观以及透过搜索引围观定位及表明记录点滴种情况。

无论是这种情景,都得通过索引的净扫描来防止排序的发出。看下的例子:

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

已选择10行。

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=20 Bytes=1580)
1 0 VIEW (Cost=26 Card=20 Bytes=1580)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=26 Card=6361 Bytes=419826)
4 3 INDEX (FULL SCAN) OF ‘IND_T_OBJECT_NAME’ (NON-UNIQUE) (Cost=26
Card=6361 Bytes=108137)

 

Statistics

0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
576 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)
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 OBJECT_NAME
8 )
9 WHERE ROWNUM <= 20
10 )
11 WHERE RN >= 11;

已选择10行。

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=43 Card=20 Bytes=1840)
1 0 VIEW (Cost=43 Card=20 Bytes=1840)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=43 Card=6361 Bytes=502519)
4 3 SORT (ORDER BY STOPKEY) (Cost=43 Card=6361 Bytes=133581)
5 4 TABLE ACCESS (FULL) OF ‘T’ (Cost=9 Card=6361 Bytes=133581)

 

Statistics

0 recursive calls
0 db block gets
81 consistent gets
0 physical reads
0 redo size
673 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

出于无可知但经过查找引围观得到查询结果,这里Oracle采纳了发明扫描。这是由起初化参数设置决定的。因而,提议于分页的当儿下FIRST_ROWS提示。

SQL> SELECT /*+ FIRST_ROWS */
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 OBJECT_NAME
8 )
9 WHERE ROWNUM <= 20
10 )
11 WHERE RN >= 11;

已选择10行。

Execution Plan

0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=826 Card=20
Bytes=1840)
1 0 VIEW (Cost=826 Card=20 Bytes=1840)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=826 Card=6361 Bytes=502519)
4 3 TABLE ACCESS (BY INDEX ROWID) OF ‘T’ (Cost=826 Card=6361
Bytes=133581)
5 4 INDEX (FULL SCAN) OF ‘IND_T_OBJECT_NAME’ (NON-UNIQUE) (Cost=26
Card=6361)

 

Statistics

0 recursive calls
0 db block gets
22 consistent gets
0 physical reads
0 redo size
673 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)
0 sorts (disk)
10 rows processed

使用了FIRST_ROWS提醒后,Oracle不欲扫描全表,而且免了排序操作。

脚研究最终一栽情形,排类别非是找引列。这么些时排序不可制止,但是下为出分页格式,Oracle不相会指向拥有数据开展排序,而是只有排序前N修记下。

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行。

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=64 Card=6361 Bytes=585212)
1 0 VIEW (Cost=64 Card=6361 Bytes=585212)
2 1 COUNT
3 2 VIEW (Cost=64 Card=6361 Bytes=502519)
4 3 SORT (ORDER BY) (Cost=64 Card=6361 Bytes=260801)
5 4 TABLE ACCESS (FULL) OF ‘T’ (Cost=9 Card=6361 Bytes=260801)

 

Statistics

0 recursive calls
0 db block gets
81 consistent gets
0 physical reads
0 redo size
690 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 WHERE ROWNUM <= 20
10 )
11 WHERE RN >= 11;

已选择10行。

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=64 Card=20 Bytes=1840)
1 0 VIEW (Cost=64 Card=20 Bytes=1840)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=64 Card=6361 Bytes=502519)
4 3 SORT (ORDER BY STOPKEY) (Cost=64 Card=6361 Bytes=260801)
5 4 TABLE ACCESS (FULL) OF ‘T’ (Cost=9 Card=6361 Bytes=260801)

 

Statistics

0 recursive calls
0 db block gets
81 consistent gets
0 physical reads
0 redo size
690 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

察二种植不同写法的ORDER
BY步骤,一个凡带来STOPKEY的ORDER
BY,另一个请勿带来。在好数据量需要排序的状态下,带STOPKEY的效能要相比非带STOPKEY排序的频率高得几近。

SQL> INSERT INTO T SELECT T.* FROM
T, USER_OBJECTS;

已创建407104行。

SQL> COMMIT;

付完。

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

已选择10行。

就就此时间: 00: 00: 03.78

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=64 Card=20 Bytes=1840)
1 0 VIEW (Cost=64 Card=20 Bytes=1840)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=64 Card=6361 Bytes=502519)
4 3 SORT (ORDER BY STOPKEY) (Cost=64 Card=6361 Bytes=260801)
5 4 TABLE ACCESS (FULL) OF ‘T’ (Cost=9 Card=6361 Bytes=260801)

 

Statistics

268 recursive calls
0 db block gets
6215 consistent gets
6013 physical reads
0 redo size
740 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 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 FROM
5 (
6 SELECT OBJECT_ID, OBJECT_NAME FROM T ORDER BY TIMESTAMP
7 )
8 )
9 WHERE RN BETWEEN 11 AND 20;

已选择10行。

现已就此时: 00: 00: 11.86

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=64 Card=6361 Bytes=585212)
1 0 VIEW (Cost=64 Card=6361 Bytes=585212)
2 1 COUNT
3 2 VIEW (Cost=64 Card=6361 Bytes=502519)
4 3 SORT (ORDER BY) (Cost=64 Card=6361 Bytes=260801)
5 4 TABLE ACCESS (FULL) OF ‘T’ (Cost=9 Card=6361 Bytes=260801)

 

Statistics

26 recursive calls
12 db block gets
6175 consistent gets
9219 physical reads
0 redo size
737 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
10 rows processed

考察五个查询语句的举行时,以及总结音信被的排序消息。对于第一个查询语句,Oracle利用了ORDER
BY STOPKEY格局开展排序,排序操作才排序需要的TOP
N的数目,因而排序操作放到了内存中,而对第二只查询语句子来说,举办的多寡的全排序,排序数据量大,排序操作不得不以磁盘上到位,因而耗时可比多。

由此下边的例子可以看给闹底标准分页查询格式,对于富含排序的操作仍旧能够在特别怪程度达增强分页查询性能。

相关文章