Oracle分页查询语句(六)

及时篇稿子通过例子表明分页查询利用的NESTED
LOOP操作,在分页查询翻至最终几页时之习性问题:

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 过程都成做到。

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 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=CHOOSE (Cost=830 Card=96985
Bytes=5819100)
1 0 VIEW (Cost=830 Card=96985 Bytes=5819100)
2 1 COUNT
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
8586 consistent gets
8052 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

以分页查询的先头几页,NESTED
LOOP操作比HASH JOIN操作效用高得几近。

SQL> SET AUTOT OFF
SQL> SELECT COUNT(*) FROM T, T1 WHERE USERNAME = OWNER;

COUNT(*)

96985

SQL> SET AUTOT TRACE

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 )
12 WHERE RN BETWEEN 96971 AND 96980;

已选择10行。

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=830 Card=96985
Bytes=5819100)
1 0 VIEW (Cost=830 Card=96985 Bytes=5819100)
2 1 COUNT
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
8586 consistent gets
8068 physical reads
0 redo size
571 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的点子,执行效能几乎没有另外移,而以NESTED
LOOP情势,则效用严重下滑,而且远远低于HASH JOIN的章程。

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 <= 96980
12 )
13 WHERE RN >= 96971;

已选择10行。

Execution Plan

0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=97811 Card=96980
Bytes=5818800)
1 0 VIEW (Cost=97811 Card=96980 Bytes=5818800)
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
105566 consistent gets
8068 physical reads
0 redo size
571 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

分页查询一般情形下,很少会翻至结尾一首,假如只是偶然境遇那种状况,对系统性能不会见发出异常非凡的熏陶,可是只要日常遇上这种状态,在设计分页查询时应该给予丰盛的考虑。

相关文章