Oracle分頁查詢語句(六)
Oracle的分頁查詢語句基本上可以按照本文給出的格式來進行套用。
Oracle分頁查詢語句(一):http://yangtingkun.itpub.net/post/468/100278
Oracle分頁查詢語句(二):http://yangtingkun.itpub.net/post/468/101703
Oracle分頁查詢語句(三):http://yangtingkun.itpub.net/post/468/104595
Oracle分頁查詢語句(四):http://yangtingkun.itpub.net/post/468/104867
Oracle分頁查詢語句(五):http://yangtingkun.itpub.net/post/468/107934
這篇文章透過例子說明分頁查詢使用的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
分頁查詢一般情況下,很少會翻到最後一篇,如果只是偶爾碰到這種情況,對系統效能不會有很大的影響,但是如果經常碰到這種情況,在設計分頁查詢時應該給予足夠的考慮。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29067253/viewspace-2141076/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- oracle分頁查詢語句Oracle
- Oracle分頁查詢語句(七)Oracle
- Oracle分頁查詢語句(五)Oracle
- Oracle分頁查詢語句(四)Oracle
- Oracle分頁查詢語句(三)Oracle
- Oracle分頁查詢語句(二)Oracle
- Oracle分頁查詢語句(一)Oracle
- 有關Oracle分頁查詢語句Oracle
- MySQL、Oracle和SQL Server的分頁查詢語句MySqlOracleServer
- Sql Server系列:查詢分頁語句SQLServer
- [Mysql 查詢語句]——分組查詢group byMySql
- golang通過mysql語句實現分頁查詢GolangMySql
- golang透過mysql語句實現分頁查詢GolangMySql
- oracle分頁查詢Oracle
- SQL查詢語句 (Oracle)SQLOracle
- oracle查詢語句大全Oracle
- Oracle的分頁查詢Oracle
- Oracle分頁查詢格式Oracle
- Oracle常用的查詢語句Oracle
- oracle--06查詢語句Oracle
- ORACLE 資料庫 查詢語句與DML語句Oracle資料庫
- Oracle分頁查詢格式(十三)Oracle
- Oracle分頁查詢格式(十二)Oracle
- Oracle分頁查詢格式(十一)Oracle
- Oracle分頁查詢格式(八)Oracle
- Oracle分頁查詢格式(九)Oracle
- Oracle分頁查詢格式(十)Oracle
- Oracle分頁查詢之order byOracle
- oracle sql分頁查詢(一)OracleSQL
- ORACLE結構化查詢語句Oracle
- oracle查詢語句查詢增加一列內容Oracle
- oracle 使用explain plan分析查詢語句OracleAI
- ORACLE 查詢語句處理過程(Oracle
- Oracle總結【SQL細節、多表查詢、分組查詢、分頁】OracleSQL
- Hql查詢語句
- mysql查詢語句MySql
- SQL分頁語句SQL
- [Mysql 查詢語句]——查詢欄位MySql