Oracle分頁查詢語句

moonboat 2007-01-28

展開全文

Oracle的分頁查詢語句基本上可以按照本文給出的格式來進(jìn)行套用。

（一）

分頁查詢格式：

SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
WHERE ROWNUM <= 40
)
WHERE RN >= 21

其中最內(nèi)層的查詢SELECT * FROM TABLE_NAME表示不進(jìn)行翻頁的原始查詢語句。ROWNUM <= 40和RN >= 21控制分頁查詢的每頁的范圍。

上面給出的這個(gè)分頁查詢語句，在大多數(shù)情況擁有較高的效率。分頁的目的就是控制輸出結(jié)果集大小，將結(jié)果盡快的返回。在上面的分頁查詢語句中，這種考慮主要體現(xiàn)在WHERE ROWNUM <= 40這句上。

選擇第21到40條記錄存在兩種方法，一種是上面例子中展示的在查詢的第二層通過ROWNUM <= 40來控制最大值，在查詢的最外層控制最小值。而另一種方式是去掉查詢第二層的WHERE ROWNUM <= 40語句，在查詢的最外層控制分頁的最小值和最大值。這是，查詢語句如下：

SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
)
WHERE RN BETWEEN 21 AND 40

對比這兩種寫法，絕大多數(shù)的情況下，第一個(gè)查詢的效率比第二個(gè)高得多。

這是由于CBO優(yōu)化模式下，Oracle可以將外層的查詢條件推到內(nèi)層查詢中，以提高內(nèi)層查詢的執(zhí)行效率。對于第一個(gè)查詢語句，第二層的查詢條件WHERE ROWNUM <= 40就可以被Oracle推入到內(nèi)層查詢中，這樣Oracle查詢的結(jié)果一旦超過了ROWNUM限制條件，就終止查詢將結(jié)果返回了。

而第二個(gè)查詢語句，由于查詢條件BETWEEN 21 AND 40是存在于查詢的第三層，而Oracle無法將第三層的查詢條件推到最內(nèi)層（即使推到最內(nèi)層也沒有意義，因?yàn)樽顑?nèi)層查詢不知道RN代表什么）。因此，對于第二個(gè)查詢語句，Oracle最內(nèi)層返回給中間層的是所有滿足條件的數(shù)據(jù)，而中間層返回給最外層的也是所有數(shù)據(jù)。數(shù)據(jù)的過濾在最外層完成，顯然這個(gè)效率要比第一個(gè)查詢低得多。

上面分析的查詢不僅僅是針對單表的簡單查詢，對于最內(nèi)層查詢是復(fù)雜的多表聯(lián)合查詢或最內(nèi)層查詢包含排序的情況一樣有效。

這里就不對包含排序的查詢進(jìn)行說明了，下一篇文章會(huì)通過例子來詳細(xì)說明。下面簡單討論一下多表聯(lián)合的情況。對于最常見的等值表連接查詢，CBO一般可能會(huì)采用兩種連接方式NESTED LOOP和HASH JOIN（MERGE JOIN效率比HASH JOIN效率低，一般CBO不會(huì)考慮）。在這里，由于使用了分頁，因此指定了一個(gè)返回的最大記錄數(shù)，NESTED LOOP在返回記錄數(shù)超過最大值時(shí)可以馬上停止并將結(jié)果返回給中間層，而HASH JOIN必須處理完所有結(jié)果集（MERGE JOIN也是）。那么在大部分的情況下，對于分頁查詢選擇NESTED LOOP作為查詢的連接方法具有較高的效率（分頁查詢的時(shí)候絕大部分的情況是查詢前幾頁的數(shù)據(jù)，越靠后面的頁數(shù)訪問幾率越小）。

因此，如果不介意在系統(tǒng)中使用HINT的話，可以將分頁的查詢語句改寫為：

SELECT /*+ FIRST_ROWS */ * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
WHERE ROWNUM <= 40
)
WHERE RN >= 21

(二)

這篇文章用幾個(gè)例子來說明分頁查詢的效率。首先構(gòu)造一個(gè)比較大的表作為測試表：

SQL> CREATE TABLE T AS SELECT * FROM DBA_OBJECTS, DBA_SEQUENCES;

表已創(chuàng)建。

SQL> SELECT COUNT(*) FROM T;

COUNT(*)
----------
457992

首先比較兩種分頁方法的區(qū)別：

SQL> SET AUTOT ON
SQL> COL OBJECT_NAME FORMAT A30
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, ‘T‘)

PL/SQL 過程已成功完成。

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 FROM T
8 )
9 )
10 WHERE RN BETWEEN 11 AND 20;

OBJECT_ID OBJECT_NAME
---------- ------------------------------
5807 ALL_APPLY_PROGRESS
1769 ALL_ARGUMENTS
2085 ALL_ASSOCIATIONS
4997 ALL_AUDIT_POLICIES
4005 ALL_BASE_TABLE_MVIEWS
5753 ALL_CAPTURE
5757 ALL_CAPTURE_PARAMETERS
5761 ALL_CAPTURE_PREPARED_DATABASE
5765 ALL_CAPTURE_PREPARED_SCHEMAS
5769 ALL_CAPTURE_PREPARED_TABLES

已選擇10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=864 Card=457992 Bytes=42135264)
1 0 VIEW (Cost=864 Card=457992 Bytes=42135264)
2 1 COUNT
3 2 TABLE ACCESS (FULL) OF ‘T‘ (Cost=864 Card=457992 Bytes=9617832)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
8979 consistent gets
7422 physical reads
0 redo size
758 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 FROM T
8 )
9 WHERE ROWNUM <= 20
10 )
11 WHERE RN >= 11;

已選擇10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=864 Card=20 Bytes=1840)
1 0 VIEW (Cost=864 Card=20 Bytes=1840)
2 1 COUNT (STOPKEY)
3 2 TABLE ACCESS (FULL) OF ‘T‘ (Cost=864 Card=457992 Bytes=9617832)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
758 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

二者執(zhí)行效率相差很大，一個(gè)需要8000多邏輯讀，而另一個(gè)只需要5個(gè)邏輯讀。觀察二者的執(zhí)行計(jì)劃可以發(fā)現(xiàn)，兩個(gè)執(zhí)行計(jì)劃唯一的區(qū)別就是第二個(gè)查詢在COUNT這步使用了STOPKEY，也就是說，Oracle將ROWNUM <= 20推入到查詢內(nèi)層，當(dāng)符合查詢的條件的記錄達(dá)到STOPKEY的值，則Oracle結(jié)束查詢。

因此，可以預(yù)見，采用第二種方式，在翻頁的開始部分查詢速度很快，越到后面，效率越低，當(dāng)翻到最后一頁，效率應(yīng)該和第一種方式接近。

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 FROM T
8 )
9 WHERE ROWNUM <= 457990
10 )
11 WHERE RN >= 457980;

OBJECT_ID OBJECT_NAME
---------- ------------------------------
7128 XCF_I_HANDLE_STATUS
7126 XCF_P
7127 XCF_U1
7142 XDF
7145 XDF_I_DF_KEY
7146 XDF_I_HANDLE_STATUS
7143 XDF_P
7144 XDF_U1
TEST.YANGTINGKUN
TEST4.YANGTINGKUN
YANGTK.YANGTINGKUN

已選擇11行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=864 Card=457990 Bytes=42135080)
1 0 VIEW (Cost=864 Card=457990 Bytes=42135080)
2 1 COUNT (STOPKEY)
3 2 TABLE ACCESS (FULL) OF ‘T‘ (Cost=864 Card=457992 Bytes=9617832)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
8979 consistent gets
7423 physical reads
0 redo size
680 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)
11 rows processed

（三）

繼續(xù)看查詢的第二種情況，包含表連接的情況：

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 過程已成功完成。

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

PL/SQL 過程已成功完成。

創(chuàng)建了T表和T1表，默認(rèn)情況下，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

但是如果分頁查詢的內(nèi)層是這種連接查詢的話，使用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效率更高，難道上面說錯(cuò)了。

其實(shí)這個(gè)現(xiàn)象是由于這個(gè)例子的特殊性造成的。T表是根據(jù)DBA_USERS創(chuàng)建，這張表很小。HASH JOIN中第一步也就是第一張表的全表掃描是無法應(yīng)用STOPKEY的，這就是上面提到的NESTED LOOP比HASH JOIN優(yōu)勢的地方。但是，這個(gè)例子中，恰好第一張表很小，對這張表的全掃描的代價(jià)極低，因此，顯得HASH JOIN效率更高。但是，這不具備共性，如果兩張表的大小相近，或者Oracle錯(cuò)誤的選擇了先掃描大表，則使用HASH JOIN的效率就會(huì)低得多。

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先掃描大表，這回結(jié)果就很明顯了。NESTED LOOP的效果要比HASH JOIN好得多。

下面，繼續(xù)比較一下兩個(gè)分頁操作的寫法，為了使結(jié)果更具有代表性，這里都采用了FIRST_ROWS提示，讓Oracle采用NESTED LOOP的方式來進(jìn)行表連接：

已選擇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

兩種寫法的效率差別極大。關(guān)鍵仍然是是否能將STOPKEY應(yīng)用到最內(nèi)層查詢中。

對于表連接來說，在寫分頁查詢的時(shí)候，可以考慮增加FIRST_ROWS提示，它有助于更快的將查詢結(jié)果返回。

其實(shí)，不光是表連接，對于所有的分頁查詢都可以加上FIRST_ROWS提示。不過需要注意的時(shí)，分頁查詢的目標(biāo)是盡快的返回前N條記錄，因此，無論是ROWNUM還是FIRST_ROWS機(jī)制都是提高前幾頁的查詢速度，對于分頁查詢的最后幾頁，采用這些機(jī)制不但無法提高查詢速度，反而會(huì)明顯降低查詢效率，對于這一點(diǎn)使用者應(yīng)該做到心中有數(shù)。

本站是提供個(gè)人知識管理的網(wǎng)絡(luò)存儲(chǔ)空間，所有內(nèi)容均由用戶發(fā)布，不代表本站觀點(diǎn)。請注意甄別內(nèi)容中的聯(lián)系方式、誘導(dǎo)購買等信息，謹(jǐn)防詐騙。如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請點(diǎn)擊一鍵舉報(bào)。

轉(zhuǎn)藏 分享

QQ空間 QQ好友新浪微博微信

獻(xiàn)花（0） +1

來自： moonboat > 《database》

舉報(bào)/認(rèn)領(lǐng)