**********************************************************************************************************
[轉(zhuǎn)載]
Oracle的分頁(yè)查詢語(yǔ)句基本上可以按照本文給出的格式來(lái)進(jìn)行套用。
Oracle分頁(yè)查詢格式（一）：
http://yangtingkun.itpub.net/post/468/100278Oracle分頁(yè)查詢格式（二）：
http://yangtingkun.itpub.net/post/468/101703Oracle分頁(yè)查詢格式（三）：
http://yangtingkun.itpub.net/post/468/104595Oracle分頁(yè)查詢格式（四）：
http://yangtingkun.itpub.net/post/468/104867Oracle分頁(yè)查詢格式（五）：
http://yangtingkun.itpub.net/post/468/107934Oracle分頁(yè)查詢格式（六）：
http://yangtingkun.itpub.net/post/468/108677Oracle分頁(yè)查詢格式（七）：
http://yangtingkun.itpub.net/post/468/109834Oracle分頁(yè)查詢格式（八）：
http://yangtingkun.itpub.net/post/468/224557Oracle分頁(yè)查詢格式（九）：
http://yangtingkun.itpub.net/post/468/224409Oracle分頁(yè)查詢格式（十）：
http://yangtingkun.itpub.net/post/468/224823Oracle分頁(yè)查詢的排序問(wèn)題：
http://yangtingkun.itpub.net/post/468/112274Oracle官網(wǎng)連接查詢優(yōu)化的說(shuō)明：
http://docs.oracle.com/cd/E11882_01/server.112/e16638/optimops.htm#i36235NESTED LOOP/HASH JOIN/SORT MERGE JOIN的區(qū)別：
http://jewfinkl.blog.163.com/blog/static/14076982012431052316/**********************************************************************************************************
根據(jù)以上文章進(jìn)行了如下的總結(jié)。
ROWNUM
可能都知道ROWNUM只適用于小于或小于等于，如果進(jìn)行等于判斷，那么只能等于1，不能進(jìn)行大于的比較。
ROWNUM是oracle系統(tǒng)順序分配為從查詢返回的行的編號(hào)，返回的第一行分配的是1，第二行是2，依此類推。
ROWNUM總是從1開(kāi)始，不管當(dāng)前的記錄是否滿足查詢結(jié)果，ROWNUM返回的值都是1，如果這條記錄的值最終滿足所有的條件，那么ROWNUM會(huì)遞加，下一條記錄的ROWNUM會(huì)返回2，否則下一條記錄的ROWNUM仍然返回1。
理解了這一點(diǎn)，就清楚為什么一般的ROWNUM大于某個(gè)值或等于某個(gè)不為1的值是無(wú)法返回結(jié)果的，因此對(duì)于每條記錄的ROWNUM都是1，而ROWNUM為1不滿足查詢的結(jié)果，所以下一條記錄的ROWNUM不會(huì)遞增，仍然是1，因此所有的記錄都不滿足條件。
分頁(yè)查詢格式1
在查詢的最外層控制分頁(yè)的最小值和最大值。查詢語(yǔ)句如下：
[sql]
SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
)
WHERE RN BETWEEN 21 AND 40
分頁(yè)查詢格式2
[sql]
SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
WHERE ROWNUM <= 40
)
WHERE RN >= 21
分頁(yè)查詢格式3
考慮到多表聯(lián)合的情況，如果不介意在系統(tǒng)中使用HINT的話，可以將分頁(yè)的查詢語(yǔ)句改寫為：
[sql]
SELECT /*+ FIRST_ROWS */ * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
WHERE ROWNUM <= 40
)
WHERE RN >= 21
效率問(wèn)題
對(duì)比這兩種寫法，絕大多數(shù)的情況下，第2個(gè)查詢的效率比第1個(gè)高得多。
這是由于CBO優(yōu)化模式下，Oracle可以將外層的查詢條件推到內(nèi)層查詢中，以提高內(nèi)層查詢的執(zhí)行效率。對(duì)于第2個(gè)查詢語(yǔ)句，第二層的查詢條件WHERE ROWNUM <= 40就可以被Oracle推入到內(nèi)層查詢中，這樣Oracle查詢的結(jié)果一旦超過(guò)了ROWNUM限制條件，就終止查詢將結(jié)果返回了。
而第1個(gè)查詢語(yǔ)句，由于查詢條件BETWEEN 21 AND 40是存在于查詢的第三層，而Oracle無(wú)法將第三層的查詢條件推到最內(nèi)層（即使推到最內(nèi)層也沒(méi)有意義，因?yàn)樽顑?nèi)層查詢不知道RN代表什么）。因此，對(duì)于第1個(gè)查詢語(yǔ)句，Oracle最內(nèi)層返回給中間層的是所有滿足條件的數(shù)據(jù)，而中間層返回給最外層的也是所有數(shù)據(jù)。數(shù)據(jù)的過(guò)濾在最外層完成，顯然這個(gè)效率要比第一個(gè)查詢低得多。
上面分析的查詢不僅僅是針對(duì)單表的簡(jiǎn)單查詢，對(duì)于最內(nèi)層查詢是復(fù)雜的多表聯(lián)合查詢或最內(nèi)層查詢包含排序的情況一樣有效。
觀察上面格式1和格式2二者的執(zhí)行計(jì)劃可以發(fā)現(xiàn)，兩個(gè)執(zhí)行計(jì)劃唯一的區(qū)別就是格式2的查詢?cè)贑OUNT這步使用了STOPKEY，也就是說(shuō)，Oracle將ROWNUM <= 20推入到查詢內(nèi)層，當(dāng)符合查詢的條件的記錄達(dá)到STOPKEY的值，則Oracle結(jié)束查詢。因此，可以預(yù)見(jiàn)，采用第二種方式，在翻頁(yè)的開(kāi)始部分查詢速度很快，越到后面，效率越低，當(dāng)翻到最后一頁(yè)，效率應(yīng)該和第一種方式接近。
分頁(yè)查詢語(yǔ)句之所以可以很快的返回結(jié)果，是因?yàn)樗哪繕?biāo)是最快的返回第一條結(jié)果。如果每頁(yè)有20條記錄，目前翻到第5頁(yè)，那么只需要返回前100條記錄都可以滿足查詢的要求了，也許還有幾萬(wàn)條記錄也符合查詢的條件，但是由于分頁(yè)的限制，在當(dāng)前的查詢中可以忽略這些數(shù)據(jù)，而只需盡快的返回前100條數(shù)據(jù)。這也是為什么在標(biāo)準(zhǔn)分頁(yè)查詢語(yǔ)句中經(jīng)常會(huì)使用FIRST_ROWS提示的原因。
對(duì)于行操作，可以在得到結(jié)果的同時(shí)將結(jié)果直接返回給上一層調(diào)用。但是對(duì)于結(jié)果集操作，Oracle必須得到結(jié)果集中所有的數(shù)據(jù)，因此分頁(yè)查詢中所帶的ROWNUM信息不起左右。如果最內(nèi)層的子查詢中包含了下面這些操作中的一個(gè)以上，則分頁(yè)查詢語(yǔ)句無(wú)法體現(xiàn)出任何的性能優(yōu)勢(shì)：UNION、UNION ALL、MINUS、INTERSECT、GROUP BY、DISTINCT、UNIQUE以及聚集函數(shù)如MAX、MIN和分析函數(shù)等。
Oracle10g的新功能GROUP BY STOPKEY，使得Oracle10g解決了GROUP BY操作分頁(yè)效率低的問(wèn)題。在10g以前，Oracle的GROUP BY操作必須完全執(zhí)行完，才能將結(jié)果返回給用戶。但是Oracle10g增加了GROUP BY STOPKEY執(zhí)行路徑，使得用戶在執(zhí)行GROUP BY操作時(shí)，可以根據(jù)STOPKEY隨時(shí)中止正在運(yùn)行的操作。這使得標(biāo)準(zhǔn)分頁(yè)函數(shù)對(duì)于GROUP BY操作重新發(fā)揮了作用。
除了這些操作以外，分頁(yè)查詢還有一個(gè)很明顯的特點(diǎn)，就是處理的頁(yè)數(shù)越小，效率就越高，越到后面，查詢速度越慢。
分頁(yè)查詢用來(lái)提高返回速度的方法都是針對(duì)數(shù)據(jù)量較小的前N條記錄而言。無(wú)論是索引掃描，NESTED LOOP連接，還是ORDER BY STOPKEY，這些方法帶來(lái)性能提升的前提都是數(shù)據(jù)量比較小，一旦分頁(yè)到了最后幾頁(yè)，會(huì)發(fā)現(xiàn)這些方法不但沒(méi)有辦法帶來(lái)性能的提升，而且性能比普通查詢還要低得多。這一點(diǎn)，在使用分頁(yè)查詢的時(shí)候，一定要心里有數(shù)。
分頁(yè)查詢一般情況下，很少會(huì)翻到最后一篇，如果只是偶爾碰到這種情況，對(duì)系統(tǒng)性能不會(huì)有很大的影響，但是如果經(jīng)常碰到這種情況，在設(shè)計(jì)分頁(yè)查詢時(shí)應(yīng)該給予足夠的考慮。
多表聯(lián)合
下面簡(jiǎn)單討論一下多表聯(lián)合的情況。對(duì)于最常見(jiàn)的等值表連接查詢，CBO一般可能會(huì)采用兩種連接方式NESTED LOOP和HASH JOIN（MERGE JOIN效率比HASH JOIN效率低，一般CBO不會(huì)考慮）。
一般對(duì)于大表查詢情況下，HASH JOIN的效率要比NESTED LOOP高很多，所以CBO一般默認(rèn)會(huì)選擇HASH JOIN.
但是如果分頁(yè)查詢的內(nèi)層是這種連接查詢的話，使用NESTED LOOP可以更快的得到前N條記錄。
在這里，由于使用了分頁(yè)，因此指定了一個(gè)返回的最大記錄數(shù)，NESTED LOOP在返回記錄數(shù)超過(guò)最大值時(shí)可以馬上停止并將結(jié)果返回給中間層，而HASH JOIN必須處理完所有結(jié)果集（MERGE JOIN也是）。那么在大部分的情況下，對(duì)于分頁(yè)查詢選擇NESTED LOOP作為查詢的連接方法具有較高的效率（分頁(yè)查詢的時(shí)候絕大部分的情況是查詢前幾頁(yè)的數(shù)據(jù)，越靠后面的頁(yè)數(shù)訪問(wèn)幾率越?。?。
HASH JOIN中第一步也就是第一張表的全表掃描是無(wú)法應(yīng)用STOPKEY的，這就是NESTED LOOP比HASH JOIN優(yōu)勢(shì)的地方。
但是，如果恰好第一張表很小，對(duì)這張表的全掃描的代價(jià)極低，會(huì)顯得HASH JOIN效率更高。
如果兩張表的大小相近，或者Oracle錯(cuò)誤的選擇了先掃描大表，則使用HASH JOIN的效率就會(huì)低得多。
因此對(duì)于表連接來(lái)說(shuō)，在寫分頁(yè)查詢的時(shí)候，可以考慮增加FIRST_ROWS提示，它會(huì)導(dǎo)致CBO選擇NESTED LOOP，有助于更快的將查詢結(jié)果返回。
其實(shí)，不光是表連接，對(duì)于所有的分頁(yè)查詢都可以加上FIRST_ROWS提示。
不過(guò)需要注意的時(shí)，分頁(yè)查詢的目標(biāo)是盡快的返回前N條記錄，因此，無(wú)論是ROWNUM還是FIRST_ROWS機(jī)制都是提高前幾頁(yè)的查詢速度，
對(duì)于分頁(yè)查詢的最后幾頁(yè)，采用HASH JOIN的方式，執(zhí)行效率幾乎沒(méi)有任何改變，而采用NESTED LOOP方式，則效率嚴(yán)重下降，而且遠(yuǎn)遠(yuǎn)低于HASH JOIN的方式。
排序列不唯一所帶來(lái)的問(wèn)題
如果用來(lái)排序的列不唯一，也就是存在值相等的行，可能會(huì)造成第一次在前10條返回記錄中，某行數(shù)據(jù)出現(xiàn)了，而第二次在11到第20條記錄中，某行數(shù)據(jù)又出現(xiàn)了。一條數(shù)據(jù)重復(fù)出現(xiàn)兩次，就必然意味著有數(shù)據(jù)在兩次查詢中都不會(huì)出現(xiàn)。
其實(shí)造成這個(gè)問(wèn)題的原因很簡(jiǎn)單，是由于排序列不唯一造成的。Oracle這里使用的排序算法不具有穩(wěn)定性，也就是說(shuō)，對(duì)于鍵值相等的數(shù)據(jù)，這種算法完成排序后，不保證這些鍵值相等的數(shù)據(jù)保持排序前的順序。
解決這個(gè)問(wèn)題其實(shí)也很簡(jiǎn)單。有兩種方法可以考慮。
1）在使用不唯一的字段排序時(shí)，后面跟一個(gè)唯一的字段。
一般在排序字段后面跟一個(gè)主鍵就可以了，如果表不存在主鍵，跟ROWID也可以。這種方法最簡(jiǎn)單，且對(duì)性能的影響最小。
2）另一種方法就是使用前面給出過(guò)多次的BETWEEN AND的方法。
這種方式由于采用表數(shù)據(jù)的全排序，每次只取全排序中的某一部分?jǐn)?shù)據(jù)，因此不會(huì)出現(xiàn)上面提到的重復(fù)數(shù)據(jù)問(wèn)題。
但是正是由于使用了全排序，而且ROWNUM信息無(wú)法推到查詢內(nèi)部，導(dǎo)致這種寫法的執(zhí)行效率很低
測(cè)試結(jié)果
下面做一些測(cè)試，按照如下步驟準(zhǔn)備數(shù)據(jù)：
[sql]
CREATE TABLE T AS SELECT * FROM DBA_USERS;
CREATE TABLE T1 AS SELECT * FROM DBA_SOURCE;
ALTER TABLE T ADD CONSTRAINT PK_T PRIMARY KEY (USERNAME);
ALTER TABLE T1 ADD CONSTRAINT FK_T1_OWNER FOREIGN KEY (OWNER) REFERENCES T(USERNAME);
CREATE INDEX IND_T1_OWNER ON T1(OWNER);
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T')
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T1')
set autotrace traceonly
set timing on
現(xiàn)在表格T中有37行數(shù)據(jù)，表格T1中有623K行數(shù)據(jù)。
比較格式1和格式2的查詢計(jì)劃
[sql]
--查詢語(yǔ)句1
SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM T1) A
)
WHERE RN BETWEEN 21 AND 40;
--查詢語(yǔ)句2
SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM T1) A
WHERE ROWNUM <= 40
)
WHERE RN >= 21;
執(zhí)行計(jì)劃執(zhí)行時(shí)間統(tǒng)計(jì)信息
查詢語(yǔ)句1
----------------------------------------------------------
Plan hash value: 3921461035
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 623K| 1231M| 2879 (1)| 00:00:35 |
|* 1 | VIEW | | 623K| 1231M| 2879 (1)| 00:00:35 |
| 2 | COUNT | | | | | |
| 3 | TABLE ACCESS FULL| T1 | 623K| 59M| 2879 (1)| 00:00:35 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN"<=40 AND "RN">=21)
00: 00: 02.401 recursive calls
0 db block gets
10441 consistent gets
10435 physical reads
0 redo size
1720 bytes sent via SQL*Net to client
431 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
20 rows processed
查詢語(yǔ)句2
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 40 | 82800 | 2 (0)| 00:00:01 |
|* 1 | VIEW | | 40 | 82800 | 2 (0)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | TABLE ACCESS FULL| T1 | 40 | 4000 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=21)
2 - filter(ROWNUM<=40)
00: 00: 00.030 recursive calls
0 db block gets
6 consistent gets
20 physical reads
0 redo size
1720 bytes sent via SQL*Net to client
431 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
20 rows processed
關(guān)聯(lián)查詢
[sql]
--查詢語(yǔ)句1
SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM T, T1 WHERE T.USERNAME = T1.OWNER) A
WHERE ROWNUM <= 40
)
WHERE RN >= 21;
--查詢語(yǔ)句2
SELECT /*+ FIRST_ROWS */ * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM T, T1 WHERE T.USERNAME = T1.OWNER) A
WHERE ROWNUM <= 40
)
WHERE RN >= 21;
--或者
SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT /*+ USE_NL(T T1) */ * FROM T, T1 WHERE T.USERNAME = T1.OWNER) A
WHERE ROWNUM <= 40
)
WHERE RN >= 21;
可以看到默認(rèn)是采用hash join,改用nested loop join方式似乎效率并沒(méi)有明顯提高，但是這是由于表T比較小只有34行，所以hash join的第一步即使對(duì)T進(jìn)行全表掃描而無(wú)法應(yīng)用stopkey，效率也很高。
執(zhí)行計(jì)劃執(zhí)行時(shí)間統(tǒng)計(jì)信息
查詢語(yǔ)句1
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 40 | 165K| 6 (17)| 00:00:01 |
|* 1 | VIEW | | 40 | 165K| 6 (17)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
|* 3 | HASH JOIN | | 40 | 12400 | 6 (17)| 00:00:01 |
| 4 | TABLE ACCESS FULL| T | 34 | 3740 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL| T1 | 40 | 4000 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=21)
2 - filter(ROWNUM<=40)
3 - access("T"."USERNAME"="T1"."OWNER")
00: 00: 00.040 recursive calls
0 db block gets
9 consistent gets
20 physical reads
0 redo size
2927 bytes sent via SQL*Net to client
431 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
20 rows processed
查詢語(yǔ)句2
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 40 | 165K| 13627 (1)| 00:02:44 |
|* 1 | VIEW | | 40 | 165K| 13627 (1)| 00:02:44 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | NESTED LOOPS | | | | | |
| 4 | NESTED LOOPS | | 623K| 124M| 13627 (1)| 00:02:44 |
| 5 | TABLE ACCESS FULL | T | 34 | 3740 | 3 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | IND_T1_OWNER | 36684 | | 91 (0)| 00:00:02 |
| 7 | TABLE ACCESS BY INDEX ROWID| T1 | 18342 | 1791K| 710 (1)| 00:00:09 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=21)
2 - filter(ROWNUM<=40)
6 - access("T"."USERNAME"="T1"."OWNER")
00: 00: 00.011 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
2927 bytes sent via SQL*Net to client
431 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
20 rows processed
現(xiàn)在增大表T，
[sql]
ALTER TABLE T MODIFY(USER_ID NULL, ACCOUNT_STATUS NULL, DEFAULT_TABLESPACE NULL,
TEMPORARY_TABLESPACE NULL, CREATED NULL, PROFILE NULL);
INSERT INTO T(USERNAME) SELECT ('USER' || LEVEL) FROM DUAL CONNECT BY LEVEL < 100000;
COMMIT;
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T')
然后重新測(cè)試語(yǔ)句1，會(huì)發(fā)現(xiàn)現(xiàn)在oracle已經(jīng)改成用nested loop join了。
因此現(xiàn)在語(yǔ)句1和語(yǔ)句2的效果等同了。可以使用 USE_HASH(T T1) HINT強(qiáng)制使用hash join，結(jié)果做下對(duì)比，會(huì)發(fā)現(xiàn)hash join的效率低于nested loop join，讀數(shù)據(jù)發(fā)生的IO（consistent gets+physical reads）大大增加了.
可以看到CBO是相當(dāng)智能了。
含排序的查詢
含排序操作的分頁(yè)查詢?？梢院?jiǎn)單的將查詢分為兩種不同情況，第一種排序列就是索引列，這種可以利用索引讀取，第二種排序列沒(méi)有索引。
第一種情況又可以細(xì)分為：完全索引掃描和通過(guò)索引掃描定位到表記錄兩種情況。無(wú)論是那種情況，都可以通過(guò)索引的全掃描來(lái)避免排序的產(chǎn)生。
第二種情況下，排序不可避免，但是利用給出分頁(yè)格式，Oracle不會(huì)對(duì)所有數(shù)據(jù)進(jìn)行排序，而是只排序前N條記錄。
[sql]
--查詢語(yǔ)句1，排序列就是索引列.注意這里需要加上OWNER IS NOT NULL,否則由于OWNER列不是NOT NULL,會(huì)導(dǎo)致索引無(wú)法使用。
SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM T1 WHERE OWNER IS NOT NULL ORDER BY OWNER) A
WHERE ROWNUM <= 40
)
WHERE RN >= 21;
--查詢語(yǔ)句2，排序列沒(méi)有索引
SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM T1 ORDER BY NAME) A
WHERE ROWNUM <= 40
)
WHERE RN >= 21;
--查詢語(yǔ)句3，排序列沒(méi)有索引
SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM T1 ORDER BY NAME) A
)
WHERE RN BETWEEN 21 AND 40;
執(zhí)行計(jì)劃執(zhí)行時(shí)間統(tǒng)計(jì)信息
查詢語(yǔ)句1
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 40 | 82800 | 4 (0)| 00:00:01 |
|* 1 | VIEW | | 40 | 82800 | 4 (0)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 40 | 82280 | 4 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| T1 | 646K| 62M| 4 (0)| 00:00:01 |
|* 5 | INDEX FULL SCAN | IND_T1_OWNER | 40 | | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=21)
2 - filter(ROWNUM<=40)
5 - filter("OWNER" IS NOT NULL)
*排序列就是索引列，可以看到通過(guò)索引的全掃描來(lái)避免了排序的產(chǎn)生。00: 00: 00.011 recursive calls
0 db block gets
8 consistent gets
1 physical reads
0 redo size
1682 bytes sent via SQL*Net to client
427 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
20 rows processed
查詢語(yǔ)句2
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 40 | 82800 | | 18077 (1)| 00:03:37 |
|* 1 | VIEW | | 40 | 82800 | | 18077 (1)| 00:03:37 |
|* 2 | COUNT STOPKEY | | | | | | |
| 3 | VIEW | | 646K| 1268M| | 18077 (1)| 00:03:37 |
|* 4 | SORT ORDER BY STOPKEY| | 646K| 62M| 72M| 18077 (1)| 00:03:37 |
| 5 | TABLE ACCESS FULL | T1 | 646K| 62M| | 3023 (1)| 00:00:37 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=21)
2 - filter(ROWNUM<=40)
4 - filter(ROWNUM<=40)
*排序列沒(méi)有索引，排序不可避免。帶STOPKEY的ORDER BY，排序操作放到了內(nèi)存中，
在大數(shù)據(jù)量需要排序的情況下，要比不帶STOPKEY排序的效率高得多。
00: 00: 01.321 recursive calls
0 db block gets
10973 consistent gets
10969 physical reads
0 redo size
2529 bytes sent via SQL*Net to client
427 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
20 rows processed
查詢語(yǔ)句3
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 646K| 1276M| | 18077 (1)| 00:03:37 |
|* 1 | VIEW | | 646K| 1276M| | 18077 (1)| 00:03:37 |
| 2 | COUNT | | | | | | |
| 3 | VIEW | | 646K| 1268M| | 18077 (1)| 00:03:37 |
| 4 | SORT ORDER BY | | 646K| 62M| 72M| 18077 (1)| 00:03:37 |
| 5 | TABLE ACCESS FULL| T1 | 646K| 62M| | 3023 (1)| 00:00:37 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN"<=40 AND "RN">=21)
*排序列沒(méi)有索引，排序不可避免，不帶STOPKEY，
進(jìn)行的數(shù)據(jù)的全排序，排序數(shù)據(jù)量大，排序操作不得不在磁盤上完成，因此耗時(shí)比較多。
00: 00: 05.3172 recursive calls
26 db block gets
10973 consistent gets
19933 physical reads
0 redo size
6489 bytes sent via SQL*Net to client
427 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
20 rows processed
排序列不唯一所帶來(lái)的問(wèn)題
[sql]
tony@ORCL1> CREATE TABLE TEST AS SELECT ROWNUM ID, A.* FROM DBA_OBJECTS A;
Table created.
tony@ORCL1> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'TEST');
PL/SQL procedure successfully completed.
tony@ORCL1> COLUMN OBJECT_NAME FORMAT A30
tony@ORCL1> SELECT * FROM
2 (
3 SELECT A.*, ROWNUM RN
4 FROM (SELECT ID, OWNER, OBJECT_NAME FROM TEST WHERE OWNER IS NOT NULL ORDER BY OWNER) A
5 WHERE ROWNUM <= 10
6 )
7 WHERE RN >= 1;
ID OWNER OBJECT_NAME RN
---------- ------------------------------ ------------------------------ ----------
69170 APEX_030200 WWV_FLOW_INIT_HTP_BUFFER 1
69179 APEX_030200 WWV_HTF 2
69178 APEX_030200 WWV_FLOW_LANG 3
69177 APEX_030200 WWV_FLOW_UTILITIES 4
69176 APEX_030200 VC4000ARRAY 5
69175 APEX_030200 WWV_FLOW_SECURITY 6
69174 APEX_030200 WWV_FLOW 7
69173 APEX_030200 HTMLDB_ITEM 8
69172 APEX_030200 WWV_FLOW_GLOBAL 9
69171 APEX_030200 WWV_FLOW_IMAGE_PREFIX 10
10 rows selected.
tony@ORCL1> SELECT * FROM
2 (
3 SELECT A.*, ROWNUM RN
4 FROM (SELECT ID, OWNER, OBJECT_NAME FROM TEST WHERE OWNER IS NOT NULL ORDER BY OWNER) A
5 WHERE ROWNUM <= 20
6 )
7 WHERE RN >= 11;
ID OWNER OBJECT_NAME RN
---------- ------------------------------ ------------------------------ ----------
69180 APEX_030200 WWV_HTP 11
69179 APEX_030200 WWV_HTF 12
69178 APEX_030200 WWV_FLOW_LANG 13
69177 APEX_030200 WWV_FLOW_UTILITIES 14
69176 APEX_030200 VC4000ARRAY 15
69175 APEX_030200 WWV_FLOW_SECURITY 16
69174 APEX_030200 WWV_FLOW 17
69173 APEX_030200 HTMLDB_ITEM 18
69172 APEX_030200 WWV_FLOW_GLOBAL 19
69171 APEX_030200 WWV_FLOW_IMAGE_PREFIX 20
10 rows selected.
--可以看到，有多個(gè)ID在兩次查詢中都出現(xiàn)了。
--通過(guò)加上ID作為排序列解決這個(gè)問(wèn)題。
tony@ORCL1> SELECT * FROM
2 (
3 SELECT A.*, ROWNUM RN
4 FROM (SELECT ID, OWNER, OBJECT_NAME FROM TEST WHERE OWNER IS NOT NULL ORDER BY OWNER, ID) A
5 WHERE ROWNUM <= 10
6 )
7 WHERE RN >= 1;
ID OWNER OBJECT_NAME RN
---------- ------------------------------ ------------------------------ ----------
69170 APEX_030200 WWV_FLOW_INIT_HTP_BUFFER 1
69171 APEX_030200 WWV_FLOW_IMAGE_PREFIX 2
69172 APEX_030200 WWV_FLOW_GLOBAL 3
69173 APEX_030200 HTMLDB_ITEM 4
69174 APEX_030200 WWV_FLOW 5
69175 APEX_030200 WWV_FLOW_SECURITY 6
69176 APEX_030200 VC4000ARRAY 7
69177 APEX_030200 WWV_FLOW_UTILITIES 8
69178 APEX_030200 WWV_FLOW_LANG 9
69179 APEX_030200 WWV_HTF 10
10 rows selected.
tony@ORCL1> SELECT * FROM
2 (
3 SELECT A.*, ROWNUM RN
4 FROM (SELECT ID, OWNER, OBJECT_NAME FROM TEST WHERE OWNER IS NOT NULL ORDER BY OWNER, ID) A
5 WHERE ROWNUM <= 20
6 )
7 WHERE RN >= 11;
ID OWNER OBJECT_NAME RN
---------- ------------------------------ ------------------------------ ----------
69180 APEX_030200 WWV_HTP 11
69181 APEX_030200 ESCAPE_SC 12
69182 APEX_030200 WWV_FLOW_META_DATA 13
69183 APEX_030200 WWV_FLOW_TEMPLATES_UTIL 14
69184 APEX_030200 WWV_RENDER_CALENDAR2 15
69185 APEX_030200 WWV_RENDER_CHART2 16
69186 APEX_030200 WWV_FLOW_CHECK 17
69187 APEX_030200 WWV_RENDER_REPORT3 18
69188 APEX_030200 WWV_FLOW_PAGE_CACHE_API 19
69189 APEX_030200 WWV_FLOW_RENDER_QUERY 20
10 rows selected.
