Oracle的分頁(yè)查詢(xún)語(yǔ)句基本上可以按照本文給出的格式來(lái)進(jìn)行套用。
Oracle分頁(yè)查詢(xún)語(yǔ)句（一）：http://yangtingkun.itpub.net/post/468/100278
Oracle分頁(yè)查詢(xún)語(yǔ)句（二）：http://yangtingkun.itpub.net/post/468/101703
Oracle分頁(yè)查詢(xún)語(yǔ)句（三）：http://yangtingkun.itpub.net/post/468/104595
Oracle分頁(yè)查詢(xún)語(yǔ)句（四）：http://yangtingkun.itpub.net/post/468/104867
Oracle分頁(yè)查詢(xún)語(yǔ)句（五）：http://yangtingkun.itpub.net/post/468/107934
這篇文章通過(guò)例子說(shuō)明分頁(yè)查詢(xún)使用的NESTED LOOP操作，在分頁(yè)查詢(xún)翻到最后幾頁(yè)時(shí)的性能問(wèn)題：
SQL> CREATE TABLE T AS SELECT * FROM DBA_USERS;
表已創(chuàng )建。
SQL> CREATE TABLE T1 AS SELECT * FROM DBA_SOURCE;
表已創(chuàng )建。
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);
索引已創(chuàng )建。
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T')
PL/SQL 過(guò)程已成功完成。
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T1')
PL/SQL 過(guò)程已成功完成。
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
在分頁(yè)查詢(xún)的前幾頁(yè)，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
對于最后幾頁(yè)，采用HASH JOIN的方式，執行效率幾乎沒(méi)有任何改變，而采用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
分頁(yè)查詢(xún)一般情況下，很少會(huì )翻到最后一篇，如果只是偶爾碰到這種情況，對系統性能不會(huì )有很大的影響，但是如果經(jīng)常碰到這種情況，在設計分頁(yè)查詢(xún)時(shí)應該給予足夠的考慮。
聯(lián)系客服
