Oracle分页查询语句(五)

Statistics

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

先是看一下,分页查询的性质比不采纳ROWNUM的情形要大多,然而,假如拿分页的克设置及12710以及12720中间,这时候再来对待一下星星种查询的效用。

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
8 FROM T
9 ORDER BY OBJECT_NAME
10 )
11 WHERE ROWNUM <= 12720
12 )
13 WHERE RN >= 12711;

已选择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)

 

Execution Plan

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

 

终极看几独例证:

对此行操作,可以以拿到结果的又将结果一贯归给上等同重合调用。可是对结果集操作,Oracle必须得结果集中具有的数据,因而分页查询中所带动的ROWNUM消息不由左右。假如尽内层的子查询中富含了底这些操作着之一个以上,则分页查询语句不可能体现出另的性优势:UNION、UNION
ALL、MINUS、INTERSECT、GROUP
BY、DISTINCT、UNIQUE以及聚集函数如MAX、MIN和剖析函数等。

COUNT(*)

12722

SQL> SET AUTOT TRACE
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
8 FROM T
9 ORDER BY OBJECT_NAME
10 )
11 WHERE ROWNUM <= 20
12 )
13 WHERE RN >= 11;

已选择10行。

Execution Plan

0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=826 Card=6361
Bytes=585212)
1 0 VIEW (Cost=826 Card=6361 Bytes=585212)
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)

 

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)

 

SQL> SET AUTOT TRACE
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;

此处大概总计一下,并略的验证分页查询语句以何时无法带来性能提高。

Execution Plan

0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=85 Card=20
Bytes=1840)
1 0 VIEW (Cost=85 Card=20 Bytes=1840)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=85 Card=12722 Bytes=1005038)
4 3 SORT (ORDER BY STOPKEY) (Cost=18 Card=12722 Bytes=267162)
5 4 UNION-ALL
6 5 TABLE ACCESS (FULL) OF ‘T’ (Cost=9 Card=6361 Bytes=133581)
7 5 TABLE ACCESS (FULL) OF ‘T’ (Cost=9 Card=6361 Bytes=133581)

 

Statistics

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

设若说全表扫描的情下,ROWNUM不起功用效果也非是老大明确的话,通过行使HINT,让Oracle使用索引围观,这时ROWNUM不起效率的力量尽管万分惊人了。

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
8 GROUP BY OBJECT_ID, OBJECT_NAME
9 ORDER BY OBJECT_NAME
10 )
11 WHERE ROWNUM <= 20
12 )
13 WHERE RN >= 11;

已选择10行。

Statistics

0 recursive calls
0 db block gets
12001 consistent gets
0 physical reads
0 redo size
612 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
8 FROM T
9 ORDER BY OBJECT_NAME
10 )
11 )
12 WHERE RN BETWEEN 12711 AND 12720;

已选择10行。

第一看UNION ALL、GROUP
BY以及分析函数使外围的ROWNUM限制对内层查询无效。

分页查询语句之所以可以很快的回来结果,是以它们的靶子是无比抢之归第一长达结果。假诺每页有20长长的记下,最近翻至第5页,那么单纯待回到前100长条记下还可以够满意查询的渴求了,也许还有几万漫长记下为合乎查询的尺度,不过由于分页的范围,在时下之查询中得以忽略那个多少,而止待尽快的回到前100长达数。那吗是为何当正规分页查询语句被平常会师使用FIRST_ROWS提醒的由。

分页查询用来提高重临速度的不二法门仍然针对性数据量较小以前N长条记下而言。无论是搜索引围观,NESTED
LOOP连接,依旧ORDER BY
STOPKEY,这几个艺术带来性能提高的前提都是数据量比较粗,一旦分页到了最终几页,会发现这个办法不但没辙带性能的升级,而且性能比经常查询还要低得多。这或多或少,在使分页查询的下,一定假若心里有数。

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 WINDOW (BUFFER) (Cost=826 Card=6361 Bytes=133581)
5 4 TABLE ACCESS (BY INDEX ROWID) OF ‘T’ (Cost=826 Card=6361
Bytes=133581)
6 5 INDEX (FULL SCAN) OF ‘IND_T_OBJECT_NAME’ (NON-UNIQUE) (Cost=26
Card=6361)

 

Statistics

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

顿时是分页查询ROWNUM起功用的状,下边看看假使内层查询包括了集操作时之景:

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
8 UNION ALL
9 SELECT OBJECT_ID, OBJECT_NAME FROM T
10 ORDER BY OBJECT_NAME
11 )
12 WHERE ROWNUM <= 20
13 )
14 WHERE RN >= 11;

已选择10行。

Statistics

0 recursive calls
0 db block gets
12002 consistent gets
0 physical reads
0 redo size
597 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> SET AUTOT OFF
SQL> SELECT COUNT(*) FROM T;

已选择10行。

Execution Plan

0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (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 (GROUP 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
322 consistent gets
0 physical reads
0 redo size
546 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 /*+ FIRST_ROWS */
OBJECT_ID, OBJECT_NAME
2 FROM
3 (
4 SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME
5 FROM
6 (
7 SELECT /*+ INDEX(T) */ OBJECT_ID, OBJECT_NAME FROM T
8 UNION ALL
9 SELECT /*+ INDEX(T) */ OBJECT_ID, OBJECT_NAME FROM T
10 ORDER BY OBJECT_NAME
11 )
12 WHERE ROWNUM <= 20
13 )
14 WHERE RN >= 11;

已选择10行。

Execution Plan

0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=1719 Card=20
Bytes=1840)
1 0 VIEW (Cost=1719 Card=20 Bytes=1840)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=1719 Card=12722 Bytes=1005038)
4 3 SORT (ORDER BY STOPKEY) (Cost=1652 Card=12722 Bytes=267162)
5 4 UNION-ALL
6 5 TABLE ACCESS (BY INDEX ROWID) OF ‘T’ (Cost=826 Card=6361
Bytes=133581)
7 6 INDEX (FULL SCAN) OF ‘IND_T_OBJECT_NAME’ (NON-UNIQUE) (Cost=26
Card=6361)
8 5 TABLE ACCESS (BY INDEX ROWID) OF ‘T’ (Cost=826 Card=6361
Bytes=133581)
9 8 INDEX (FULL SCAN) OF ‘IND_T_OBJECT_NAME’ (NON-UNIQUE) (Cost=26
Card=6361)

 

Statistics

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

不费吹灰之力窥见,对于第二只查询,效率及率先不好举行了同,然而分页查询的频率则大大的减退,而且这时候的效能远小于没有行使ROWNUM的查询。

前的各类例子已经证实了分页查询语句的科班写法所带来的属性提高。

Statistics

0 recursive calls
0 db block gets
23 consistent gets
0 physical reads
0 redo size
597 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
8 FROM T
9 ORDER BY OBJECT_NAME
10 )
11 )
12 WHERE RN BETWEEN 11 AND 20;

已选择10行。

Statistics

0 recursive calls
0 db block gets
161 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

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, ROW_NUMBER() OVER(ORDER BY
OBJECT_NAME)
8 FROM T
9 ORDER BY OBJECT_NAME
10 )
11 WHERE ROWNUM <= 20
12 )
13 WHERE RN >= 11;

已选择10行。

Execution Plan

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

 

相关文章