Oracle分页查询语句(三)

此起彼伏羁押查询的老二种植状态,包含表连接的情:

SQL> CREATE TABLE T AS SELECT * FROM
DBA_USERS;

发明已创立。

SQL> CREATE TABLE T1 AS SELECT *
FROM DBA_SOURCE;

注解已创造。

SQL> ALTER TABLE T ADD CONSTRAINT
PK_T PRIMARY KEY (USERNAME);

申就改。

SQL> ALTER TABLE T1 ADD CONSTRAINT
FK_T1_OWNER FOREIGN KEY (OWNER)
2 REFERENCES T(USERNAME);

表就改变。

SQL> CREATE INDEX IND_T1_OWNER ON
T1(NAME);

目录已创建。

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

PL/SQL 过程都成功做到。

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

PL/SQL 过程就成功就。

成立了T表和T1表,默认情状下,HASH
JOIN的频率要比NESTED LOOP高多:

SQL> SET AUTOT TRACE
SQL> SELECT * FROM T, T1 WHERE T.USERNAME = T1.OWNER;

已选择96985行。

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=844 Card=96985
Bytes=46164860)
1 0 HASH JOIN (Cost=844 Card=96985 Bytes=46164860)
2 1 TABLE ACCESS (FULL) OF ‘T’ (Cost=2 Card=12 Bytes=1044)
3 1 TABLE ACCESS (FULL) OF ‘T1’ (Cost=826 Card=96985
Bytes=37727165)

 

Statistics

39 recursive calls
0 db block gets
14475 consistent gets
7279 physical reads
0 redo size
37565579 bytes sent via SQL*Net to client
71618 bytes received via SQL*Net from client
6467 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
96985 rows processed

SQL> SELECT /*+ FIRST_ROWS */ *
FROM T, T1 WHERE T.USERNAME = T1.OWNER;

已选择96985行。

Execution Plan

0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=97811 Card=96985
Bytes=46164860)
1 0 NESTED LOOPS (Cost=97811 Card=96985 Bytes=46164860)
2 1 TABLE ACCESS (FULL) OF ‘T1’ (Cost=826 Card=96985 Bytes=37727165)
3 1 TABLE ACCESS (BY INDEX ROWID) OF ‘T’ (Cost=1 Card=1 Bytes=87)
4 3 INDEX (UNIQUE SCAN) OF ‘PK_T’ (UNIQUE)

 

Statistics

0 recursive calls
0 db block gets
117917 consistent gets
7268 physical reads
0 redo size
37565579 bytes sent via SQL*Net to client
71618 bytes received via SQL*Net from client
6467 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
96985 rows processed

可一旦分页查询的内层是这种连接查询的话,使用NESTED
LOOP可以还快之拿到前N久记下。

下看一下那种情景下的分页查询情形:

SQL> SELECT USER_ID, USERNAME,
NAME
2 FROM
3 (
4 SELECT ROWNUM RN, USER_ID, USERNAME, NAME
5 FROM
6 (
7 SELECT T.USER_ID, T.USERNAME, T1.NAME
8 FROM T, T1
9 WHERE T.USERNAME = T1.OWNER
10 )
11 WHERE ROWNUM <= 20
12 )
13 WHERE RN >= 11;

已选择10行。

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=830 Card=20 Bytes=1200)
1 0 VIEW (Cost=830 Card=20 Bytes=1200)
2 1 COUNT (STOPKEY)
3 2 HASH JOIN (Cost=830 Card=96985 Bytes=2909550)
4 3 TABLE ACCESS (FULL) OF ‘T’ (Cost=2 Card=12 Bytes=132)
5 3 TABLE ACCESS (FULL) OF ‘T1’ (Cost=826 Card=96985 Bytes=1842715)

Statistics

0 recursive calls
0 db block gets
8 consistent gets
7 physical reads
0 redo size
574 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 /*+ FIRST_ROWS */
USER_ID, USERNAME, NAME
2 FROM
3 (
4 SELECT ROWNUM RN, USER_ID, USERNAME, NAME
5 FROM
6 (
7 SELECT T.USER_ID, T.USERNAME, T1.NAME
8 FROM T, T1
9 WHERE T.USERNAME = T1.OWNER
10 )
11 WHERE ROWNUM <= 20
12 )
13 WHERE RN >= 11;

已选择10行。

Execution Plan

0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=97811 Card=20
Bytes=1200)
1 0 VIEW (Cost=97811 Card=20 Bytes=1200)
2 1 COUNT (STOPKEY)
3 2 NESTED LOOPS (Cost=97811 Card=96985 Bytes=2909550)
4 3 TABLE ACCESS (FULL) OF ‘T1’ (Cost=826 Card=96985 Bytes=1842715)
5 3 TABLE ACCESS (BY INDEX ROWID) OF ‘T’ (Cost=1 Card=1 Bytes=11)
6 5 INDEX (UNIQUE SCAN) OF ‘PK_T’ (UNIQUE)

 

Statistics

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

看起来像HASH
JOIN效用更胜,难道下面说错了。

实则那景是由斯事例的特殊性造成的。T表是冲DBA_USERS创造,这张表很有点。HASH
JOIN中首先步也即是率先张表的全表扫描是心有余而力不足使STOPKEY的,这即是者提到的NESTED
LOOP比HASH
JOIN优势的地点。不过,这多少个事例中,恰好第一摆设表很粗,对这张表的都扫描的代价最低,因而,显得HASH
JOIN成效更胜。不过,这不具有共性,假使少布置表底轻重相近,或者Oracle错误的精选了先行扫描大表,则以HASH
JOIN的频率就是相会低得多。

SQL> SELECT USER_ID, USERNAME,
NAME
2 FROM
3 (
4 SELECT ROWNUM RN, USER_ID, USERNAME, NAME
5 FROM
6 (
7 SELECT /*+ ORDERED */ T.USER_ID, T.USERNAME, T1.NAME
8 FROM T1, T
9 WHERE T.USERNAME = T1.OWNER
10 )
11 WHERE ROWNUM <= 20
12 )
13 WHERE RN >= 11;

已选择10行。

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=951 Card=20 Bytes=1200)
1 0 VIEW (Cost=951 Card=20 Bytes=1200)
2 1 COUNT (STOPKEY)
3 2 HASH JOIN (Cost=951 Card=96985 Bytes=2909550)
4 3 TABLE ACCESS (FULL) OF ‘T1’ (Cost=826 Card=96985 Bytes=1842715)
5 3 TABLE ACCESS (FULL) OF ‘T’ (Cost=2 Card=12 Bytes=132)

 

Statistics

0 recursive calls
0 db block gets
8585 consistent gets
7310 physical reads
0 redo size
601 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

经HINT提醒,让Oracle先扫描大表,这拨结果虽杀显著了。NESTED
LOOP的效果使相比HASH JOIN好得差不多。

下边,继续于一下点儿只分页操作的写法,为了使结果再有代表性,这里还以了FIRST_ROWS指示,让Oracle采取NESTED
LOOP的点子来拓展表达连接:

SQL> SELECT /*+ FIRST_ROWS */
USER_ID, USERNAME, NAME
2 FROM
3 (
4 SELECT ROWNUM RN, USER_ID, USERNAME, NAME
5 FROM
6 (
7 SELECT T.USER_ID, T.USERNAME, T1.NAME
8 FROM T, T1
9 WHERE T.USERNAME = T1.OWNER
10 )
11 WHERE ROWNUM <= 20
12 )
13 WHERE RN >= 11;

已选择10行。

Execution Plan

0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=97811 Card=20
Bytes=1200)
1 0 VIEW (Cost=97811 Card=20 Bytes=1200)
2 1 COUNT (STOPKEY)
3 2 NESTED LOOPS (Cost=97811 Card=96985 Bytes=2909550)
4 3 TABLE ACCESS (FULL) OF ‘T1’ (Cost=826 Card=96985 Bytes=1842715)
5 3 TABLE ACCESS (BY INDEX ROWID) OF ‘T’ (Cost=1 Card=1 Bytes=11)
6 5 INDEX (UNIQUE SCAN) OF ‘PK_T’ (UNIQUE)

 

Statistics

0 recursive calls
0 db block gets
28 consistent gets
0 physical reads
0 redo size
574 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 /*+ FIRST_ROWS */
USER_ID, USERNAME, NAME
2 FROM
3 (
4 SELECT ROWNUM RN, USER_ID, USERNAME, NAME
5 FROM
6 (
7 SELECT T.USER_ID, T.USERNAME, T1.NAME
8 FROM T, T1
9 WHERE T.USERNAME = T1.OWNER
10 )
11 )
12 WHERE RN BETWEEN 11 AND 20;

已选择10行。

Execution Plan

0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=97811 Card=96985
Bytes=5819100)
1 0 VIEW (Cost=97811 Card=96985 Bytes=5819100)
2 1 COUNT
3 2 NESTED LOOPS (Cost=97811 Card=96985 Bytes=2909550)
4 3 TABLE ACCESS (FULL) OF ‘T1’ (Cost=826 Card=96985 Bytes=1842715)
5 3 TABLE ACCESS (BY INDEX ROWID) OF ‘T’ (Cost=1 Card=1 Bytes=11)
6 5 INDEX (UNIQUE SCAN) OF ‘PK_T’ (UNIQUE)

 

Statistics

0 recursive calls
0 db block gets
105571 consistent gets
7299 physical reads
0 redo size
574 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

些微种植写法的效用差距大。关键仍然是否会拿STOPKEY应用及最好内层查询中。

对表连接来说,在描绘分页查询的时刻,可以设想多FIRST_ROWS提示,它助长重新快之拿查询结果重返。

实际上,不光是表明连接,对于有所的分页查询都得添加FIRST_ROWS指示。可是需要小心的时,分页查询的目标是及早的返前N长条记下,由此,无论是ROWNUM仍旧FIRST_ROWS机制都是增强前几乎页的询问速度,对于分页查询的末梢几乎页,采纳这么些机制不仅不可以增强查询速度,反而会显明降低查询效用,对于当下一点使用者应当完成心中有数。

相关文章