Oracle ROWNUM用法和分頁查詢總結(jié)

青_春 2016-04-07

展開全文

**********************************************************************************************************

[轉(zhuǎn)載]

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

Oracle分頁查詢格式（一）：http://yangtingkun./post/468/100278

Oracle分頁查詢格式（二）：http://yangtingkun./post/468/101703

Oracle分頁查詢格式（三）：http://yangtingkun./post/468/104595

Oracle分頁查詢格式（四）：http://yangtingkun./post/468/104867

Oracle分頁查詢格式（五）：http://yangtingkun./post/468/107934

Oracle分頁查詢格式（六）：http://yangtingkun./post/468/108677

Oracle分頁查詢格式（七）：http://yangtingkun./post/468/109834

Oracle分頁查詢格式（八）：http://yangtingkun./post/468/224557

Oracle分頁查詢格式（九）：http://yangtingkun./post/468/224409

Oracle分頁查詢格式（十）：http://yangtingkun./post/468/224823

Oracle分頁查詢的排序問題：http://yangtingkun./post/468/112274

Oracle官網(wǎng)連接查詢優(yōu)化的說明：http://docs.oracle.com/cd/E11882_01/server.112/e16638/optimops.htm#i36235
NESTED 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開始，不管當(dāng)前的記錄是否滿足查詢結(jié)果，ROWNUM返回的值都是1，如果這條記錄的值最終滿足所有的條件，那么ROWNUM會(huì)遞加，下一條記錄的ROWNUM會(huì)返回2，否則下一條記錄的ROWNUM仍然返回1。
理解了這一點(diǎn)，就清楚為什么一般的ROWNUM大于某個(gè)值或等于某個(gè)不為1的值是無法返回結(jié)果的，因此對(duì)于每條記錄的ROWNUM都是1，而ROWNUM為1不滿足查詢的結(jié)果，所以下一條記錄的ROWNUM不會(huì)遞增，仍然是1，因此所有的記錄都不滿足條件。

分頁查詢格式1
在查詢的最外層控制分頁的最小值和最大值。查詢語句如下：

[sql] view plain copy

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

分頁查詢格式2

[sql] view plain copy

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

分頁查詢格式3
考慮到多表聯(lián)合的情況，如果不介意在系統(tǒng)中使用HINT的話，可以將分頁的查詢語句改寫為：

[sql] view plain copy

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

效率問題
對(duì)比這兩種寫法，絕大多數(shù)的情況下，第2個(gè)查詢的效率比第1個(gè)高得多。
這是由于CBO優(yōu)化模式下，Oracle可以將外層的查詢條件推到內(nèi)層查詢中，以提高內(nèi)層查詢的執(zhí)行效率。對(duì)于第2個(gè)查詢語句，第二層的查詢條件WHERE ROWNUM <= 40就可以被Oracle推入到內(nèi)層查詢中，這樣Oracle查詢的結(jié)果一旦超過了ROWNUM限制條件，就終止查詢將結(jié)果返回了。
而第1個(gè)查詢語句，由于查詢條件BETWEEN 21 AND 40是存在于查詢的第三層，而Oracle無法將第三層的查詢條件推到最內(nèi)層（即使推到最內(nèi)層也沒有意義，因?yàn)樽顑?nèi)層查詢不知道RN代表什么）。因此，對(duì)于第1個(gè)查詢語句，Oracle最內(nèi)層返回給中間層的是所有滿足條件的數(shù)據(jù)，而中間層返回給最外層的也是所有數(shù)據(jù)。數(shù)據(jù)的過濾在最外層完成，顯然這個(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，也就是說，Oracle將ROWNUM <= 20推入到查詢內(nèi)層，當(dāng)符合查詢的條件的記錄達(dá)到STOPKEY的值，則Oracle結(jié)束查詢。因此，可以預(yù)見，采用第二種方式，在翻頁的開始部分查詢速度很快，越到后面，效率越低，當(dāng)翻到最后一頁，效率應(yīng)該和第一種方式接近。

分頁查詢語句之所以可以很快的返回結(jié)果，是因?yàn)樗哪繕?biāo)是最快的返回第一條結(jié)果。如果每頁有20條記錄，目前翻到第5頁，那么只需要返回前100條記錄都可以滿足查詢的要求了，也許還有幾萬條記錄也符合查詢的條件，但是由于分頁的限制，在當(dāng)前的查詢中可以忽略這些數(shù)據(jù)，而只需盡快的返回前100條數(shù)據(jù)。這也是為什么在標(biāo)準(zhǔn)分頁查詢語句中經(jīng)常會(huì)使用FIRST_ROWS提示的原因。
對(duì)于行操作，可以在得到結(jié)果的同時(shí)將結(jié)果直接返回給上一層調(diào)用。但是對(duì)于結(jié)果集操作，Oracle必須得到結(jié)果集中所有的數(shù)據(jù)，因此分頁查詢中所帶的ROWNUM信息不起左右。如果最內(nèi)層的子查詢中包含了下面這些操作中的一個(gè)以上，則分頁查詢語句無法體現(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操作分頁效率低的問題。在10g以前，Oracle的GROUP BY操作必須完全執(zhí)行完，才能將結(jié)果返回給用戶。但是Oracle10g增加了GROUP BY STOPKEY執(zhí)行路徑，使得用戶在執(zhí)行GROUP BY操作時(shí)，可以根據(jù)STOPKEY隨時(shí)中止正在運(yùn)行的操作。這使得標(biāo)準(zhǔn)分頁函數(shù)對(duì)于GROUP BY操作重新發(fā)揮了作用。

除了這些操作以外，分頁查詢還有一個(gè)很明顯的特點(diǎn)，就是處理的頁數(shù)越小，效率就越高，越到后面，查詢速度越慢。
分頁查詢用來提高返回速度的方法都是針對(duì)數(shù)據(jù)量較小的前N條記錄而言。無論是索引掃描，NESTED LOOP連接，還是ORDER BY STOPKEY，這些方法帶來性能提升的前提都是數(shù)據(jù)量比較小，一旦分頁到了最后幾頁，會(huì)發(fā)現(xiàn)這些方法不但沒有辦法帶來性能的提升，而且性能比普通查詢還要低得多。這一點(diǎn)，在使用分頁查詢的時(shí)候，一定要心里有數(shù)。
分頁查詢一般情況下，很少會(huì)翻到最后一篇，如果只是偶爾碰到這種情況，對(duì)系統(tǒng)性能不會(huì)有很大的影響，但是如果經(jīng)常碰到這種情況，在設(shè)計(jì)分頁查詢時(shí)應(yīng)該給予足夠的考慮。

多表聯(lián)合
下面簡(jiǎn)單討論一下多表聯(lián)合的情況。對(duì)于最常見的等值表連接查詢，CBO一般可能會(huì)采用兩種連接方式NESTED LOOP和HASH JOIN（MERGE JOIN效率比HASH JOIN效率低，一般CBO不會(huì)考慮）。
一般對(duì)于大表查詢情況下，HASH JOIN的效率要比NESTED LOOP高很多，所以CBO一般默認(rèn)會(huì)選擇HASH JOIN.

但是如果分頁查詢的內(nèi)層是這種連接查詢的話，使用NESTED LOOP可以更快的得到前N條記錄。
在這里，由于使用了分頁，因此指定了一個(gè)返回的最大記錄數(shù)，NESTED LOOP在返回記錄數(shù)超過最大值時(shí)可以馬上停止并將結(jié)果返回給中間層，而HASH JOIN必須處理完所有結(jié)果集（MERGE JOIN也是）。那么在大部分的情況下，對(duì)于分頁查詢選擇NESTED LOOP作為查詢的連接方法具有較高的效率（分頁查詢的時(shí)候絕大部分的情況是查詢前幾頁的數(shù)據(jù)，越靠后面的頁數(shù)訪問幾率越?。?。

HASH JOIN中第一步也就是第一張表的全表掃描是無法應(yīng)用STOPKEY的，這就是NESTED LOOP比HASH JOIN優(yōu)勢(shì)的地方。
但是，如果恰好第一張表很小，對(duì)這張表的全掃描的代價(jià)極低，會(huì)顯得HASH JOIN效率更高。
如果兩張表的大小相近，或者Oracle錯(cuò)誤的選擇了先掃描大表，則使用HASH JOIN的效率就會(huì)低得多。

因此對(duì)于表連接來說，在寫分頁查詢的時(shí)候，可以考慮增加FIRST_ROWS提示，它會(huì)導(dǎo)致CBO選擇NESTED LOOP，有助于更快的將查詢結(jié)果返回。
其實(shí)，不光是表連接，對(duì)于所有的分頁查詢都可以加上FIRST_ROWS提示。
不過需要注意的時(shí)，分頁查詢的目標(biāo)是盡快的返回前N條記錄，因此，無論是ROWNUM還是FIRST_ROWS機(jī)制都是提高前幾頁的查詢速度，
對(duì)于分頁查詢的最后幾頁，采用HASH JOIN的方式，執(zhí)行效率幾乎沒有任何改變，而采用NESTED LOOP方式，則效率嚴(yán)重下降，而且遠(yuǎn)遠(yuǎn)低于HASH JOIN的方式。

排序列不唯一所帶來的問題
如果用來排序的列不唯一，也就是存在值相等的行，可能會(huì)造成第一次在前10條返回記錄中，某行數(shù)據(jù)出現(xiàn)了，而第二次在11到第20條記錄中，某行數(shù)據(jù)又出現(xiàn)了。一條數(shù)據(jù)重復(fù)出現(xiàn)兩次，就必然意味著有數(shù)據(jù)在兩次查詢中都不會(huì)出現(xiàn)。
其實(shí)造成這個(gè)問題的原因很簡(jiǎn)單，是由于排序列不唯一造成的。Oracle這里使用的排序算法不具有穩(wěn)定性，也就是說，對(duì)于鍵值相等的數(shù)據(jù)，這種算法完成排序后，不保證這些鍵值相等的數(shù)據(jù)保持排序前的順序。
解決這個(gè)問題其實(shí)也很簡(jiǎn)單。有兩種方法可以考慮。
1）在使用不唯一的字段排序時(shí)，后面跟一個(gè)唯一的字段。
一般在排序字段后面跟一個(gè)主鍵就可以了，如果表不存在主鍵，跟ROWID也可以。這種方法最簡(jiǎn)單，且對(duì)性能的影響最小。
2）另一種方法就是使用前面給出過多次的BETWEEN AND的方法。
這種方式由于采用表數(shù)據(jù)的全排序，每次只取全排序中的某一部分?jǐn)?shù)據(jù)，因此不會(huì)出現(xiàn)上面提到的重復(fù)數(shù)據(jù)問題。
但是正是由于使用了全排序，而且ROWNUM信息無法推到查詢內(nèi)部，導(dǎo)致這種寫法的執(zhí)行效率很低

測(cè)試結(jié)果
下面做一些測(cè)試，按照如下步驟準(zhǔn)備數(shù)據(jù)：

[sql] view plain copy

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] view plain copy

--查詢語句1
SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM T1) A
)
WHERE RN BETWEEN 21 AND 40;
--查詢語句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ì)信息

查詢語句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.40

1 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

查詢語句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.03

0 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] view plain copy

--查詢語句1
SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM T, T1 WHERE T.USERNAME = T1.OWNER) A
WHERE ROWNUM <= 40
)
WHERE RN >= 21;
--查詢語句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方式似乎效率并沒有明顯提高，但是這是由于表T比較小只有34行，所以hash join的第一步即使對(duì)T進(jìn)行全表掃描而無法應(yīng)用stopkey，效率也很高。

執(zhí)行計(jì)劃

執(zhí)行時(shí)間

統(tǒng)計(jì)信息

查詢語句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.04

0 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

查詢語句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.01

1 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] view plain copy

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è)試語句1，會(huì)發(fā)現(xiàn)現(xiàn)在oracle已經(jīng)改成用nested loop join了。
因此現(xiàn)在語句1和語句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)智能了。

含排序的查詢
含排序操作的分頁查詢?？梢院?jiǎn)單的將查詢分為兩種不同情況，第一種排序列就是索引列，這種可以利用索引讀取，第二種排序列沒有索引。
第一種情況又可以細(xì)分為：完全索引掃描和通過索引掃描定位到表記錄兩種情況。無論是那種情況，都可以通過索引的全掃描來避免排序的產(chǎn)生。
第二種情況下，排序不可避免，但是利用給出分頁格式，Oracle不會(huì)對(duì)所有數(shù)據(jù)進(jìn)行排序，而是只排序前N條記錄。

[sql] view plain copy

--查詢語句1，排序列就是索引列.注意這里需要加上OWNER IS NOT NULL,否則由于OWNER列不是NOT NULL,會(huì)導(dǎo)致索引無法使用。
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;
--查詢語句2，排序列沒有索引
SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM T1 ORDER BY NAME) A
WHERE ROWNUM <= 40
)
WHERE RN >= 21;
--查詢語句3，排序列沒有索引
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ì)信息
查詢語句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) *排序列就是索引列，可以看到通過索引的全掃描來避免了排序的產(chǎn)生。	00: 00: 00.01	1 recursive calls 0 db block gets 8 consistent gets 1 physical reads 0 redo size 1682 bytes sent via SQLNet to client 427 bytes received via SQLNet from client 3 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 20 rows processed
查詢語句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) *排序列沒有索引，排序不可避免。帶STOPKEY的ORDER BY，排序操作放到了內(nèi)存中，在大數(shù)據(jù)量需要排序的情況下，要比不帶STOPKEY排序的效率高得多。	00: 00: 01.32	1 recursive calls 0 db block gets 10973 consistent gets 10969 physical reads 0 redo size 2529 bytes sent via SQLNet to client 427 bytes received via SQLNet from client 3 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 20 rows processed
查詢語句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) *排序列沒有索引，排序不可避免，不帶STOPKEY，進(jìn)行的數(shù)據(jù)的全排序，排序數(shù)據(jù)量大，排序操作不得不在磁盤上完成，因此耗時(shí)比較多。	00: 00: 05.31	72 recursive calls 26 db block gets 10973 consistent gets 19933 physical reads 0 redo size 6489 bytes sent via SQLNet to client 427 bytes received via SQLNet from client 3 SQL*Net roundtrips to/from client 0 sorts (memory) 1 sorts (disk) 20 rows processed

排序列不唯一所帶來的問題

[sql] view plain copy

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)了。
--通過加上ID作為排序列解決這個(gè)問題。
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.

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

轉(zhuǎn)藏 分享

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

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

來自：青_春 > 《Oracle》

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