Oracle分查詢優(yōu)化

1、Oracle 的分頁查詢語句基本上可以按照本文給出的格式來進(jìn)行套用Oracle 分頁查詢語句（一）分頁查詢格式：SELECT*FROM（SELECTA.*,ROWNUMRNFROM（SELECT*FROMTABLE_NAME）AWHEREROWNUM=21其中最內(nèi)層的查詢 SELECT*FROMTABLE_NAME 表示不進(jìn)行翻頁的原始查詢語句。ROWNUM=21 控制分頁查詢的每頁的范圍。上面給出的這個(gè)分頁查詢語句，在大多數(shù)情況擁有較高的效率。分頁的目的就是控制輸出結(jié)果集大小，將結(jié)果盡快的返回。在上面的分頁查詢語句中，這種考慮主要體現(xiàn)在 WHEREROWNUM=40 這句上。選擇第 21 到

2、 40 條記錄存在兩種方法，一種是上面例子中展示的在查詢的第二層通過 ROWNUM=40 來控制最大值，在查詢的最外層控制最小值。而另一種方式是去掉查詢第二層的 WHEREROWNUM=40語句，在查詢的最外層控制分頁的最小值和最大值。這是，查詢語句如下：SELECT*FROM（SELECTA.*,ROWNUMRNFROM（SELECT*FROMTABLE_NAME）A）WHERERNBETWEEN21AND40對比這兩種寫法，絕大多數(shù)的情況下，第一個(gè)查詢的效率比第二個(gè)高得多。這是由于 CBO 優(yōu)化模式下，Oracle 可以將外層的查詢條件推到內(nèi)層查詢中，以提高內(nèi)層查詢的執(zhí)行效率。對于第一個(gè)查

3、詢語句，第二層的查詢條件 WHEREROWNUM=40 就可以被 Oracle 推入到內(nèi)層查詢中，這樣 Oracle 查詢的結(jié)果一旦超過了 ROWNUM 限制條件，就終止查詢將結(jié)果返回了。而第二個(gè)查詢語句，由于查詢條件 BETWEEN21AND40 是存在于查詢的第三層，而 Oracle 無法將第三層的查詢條件推到最內(nèi)層（即使推到最內(nèi)層也沒有意義，因?yàn)樽顑?nèi)層查詢不知道 RN 代表什么）。因此，對于第二個(gè)查詢語句，Oracle 最內(nèi)層返回給中間層的是所有滿足條件的數(shù)據(jù)，而中間層返回給最外層的也是所有數(shù)據(jù)。數(shù)據(jù)的過濾在最外層完成，顯然這個(gè)效率要比第一個(gè)查詢低得多。上面分析的查詢不僅僅是針對單表的簡

4、單查詢， 對于最內(nèi)層查詢是復(fù)雜的多表聯(lián)合查詢或最內(nèi)層查詢包含排序的情況一樣有效。這里就不對包含排序的查詢進(jìn)行說明了，下一篇文章會通過例子來詳細(xì)說明。下面簡單討論一下多表聯(lián)合的情況。對于最常見的等彳！表連接查詢，CBO 一般可能會采用兩種連接方式 NESTEDLOOP 和 HASHJOIN（MERGEJOIN 效率比 HASHJOIN 效率低，一般 CBO 不會考慮）。在這里，由于使用了分頁，因此指定了一個(gè)返回的最大記錄數(shù)，NESTEDLOOP 在返回記錄數(shù)超過最大值時(shí)可以馬上停止并將結(jié)果返回給中間層，而 HASHJOIN 必須處理完所有結(jié)果集（MERGEJOIN 也是）。那么在大部分的情況下，

5、對于分頁查詢選擇 NESTEDLOOP 作為查詢的連接方法具有較高的效率（分頁查詢的時(shí)候絕大部分的情況是查詢前幾頁的數(shù)據(jù)，越靠后面的頁數(shù)訪問幾率越?。Ｒ虼?，如果不介意在系統(tǒng)中使用 HINT 的話，可以將分頁的查詢語句改寫為：SELECT/*+FIRST_ROWS*/*FROM(SELECTA.*,ROWNUMRNFROM(SELECT*FROMTABLE_NAME)AWHEREROWNUM=21Oracle 分頁查詢語句(二)這篇文章用幾個(gè)例子來說明分頁查詢的效率。首先構(gòu)造一個(gè)比較大的表作為測試表：SQLCREATETABLETASSELECT*FROMDBA_OBJECTS,DBA_SEQ

6、UENCES;表已創(chuàng)建。SQLSELECTCOUNT(*)FROMT;COUNT(*)457992首先比較兩種分頁方法的區(qū)別：SQLSETAUTOTONSQLCOLOBJECT_NAMEFORMATA30SQLEXECDBMS_STATS.GATHER_TABLE_STATS(USER,T)PL/SQL 過程已成功完成。SQLSELECTOBJECT_ID,OBJECT_NAME2 FROM3 (4 SELECTROWNUMRN,OBJECT_ID,OBJECT_NAME5 FROM6 (7 SELECTOBJECT_ID,OBJECT_NAMEFROMT8 )9 )10 WHERERNBET

7、WEEN11AND20;OBJECT_IDOBJECT_NAME5807ALL_APPLY_PROGRESS1769ALL_ARGUMENTS2085ALL_ASSOCIATIONS4997ALL_AUDIT_POLICIES4005ALL_BASE_TABLE_MVIEWS5753ALL_CAPTURE5757ALL_CAPTURE_PARAMETERS5761ALL_CAPTURE_PREPARED_DATABASE5765ALL_CAPTURE_PREPARED_SCHEMASExecutionPlan0SELECTSTATEMENTOptimizer=CHOOSE(Cost=864Ca

8、rd=457992Bytes=42135264)10VIEW(Cost=864Card=457992Bytes=42135264)21COUNT32TABLEACCESS(FULL)OFT(Cost=864Card=457992Bytes=9617832)Statistics0recursivecalls0dbblockgets8979consistentgets7422physicalreads0redosize758bytessentviaSQL*Nettoclient503bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/from

9、client0sorts(memory)0sorts(disk)10rowsprocessedSQLSELECTOBJECT_ID,OBJECT_NAME2 FROM3 (4 SELECTROWNUMRN,OBJECT_ID,OBJECT_NAME5 FROM6 (7 SELECTOBJECT_ID,OBJECT_NAMEFROMT8 )9 WHEREROWNUM=11;OBJECT_IDOBJECT_NAME5807ALL_APPLY_PROGRESS1769ALL_ARGUMENTS2085ALL_ASSOCIATIONS4997ALL_AUDIT_POLICIES4005ALL_BASE

10、_TABLE_MVIEWS5753ALL_CAPTURE5757ALL_CAPTURE_PARAMETERS5761ALL_CAPTURE_PREPARED_DATABASE5765ALL_CAPTURE_PREPARED_SCHEMASExecutionPlan0SELECTSTATEMENTOptimizer=CHOOSE(Cost=864Card=20Bytes=1840)0 0VIEW(Cost=864Card=20Bytes=1840)0 1COUNT(STOPKEY)32TABLEACCESS(FULL)OFT(Cost=864Card=457992Bytes=9617832)St

11、atistics0 recursivecalls0 dbblockgets0 consistentgets0 physicalreads0 redosize758bytessentviaSQL*Nettoclient503bytesreceivedviaSQL*Netfromclient0 SQL*Netroundtripsto/fromclient0 sorts(memory)0 sorts(disk)0 0rowsprocessed二者執(zhí)行效率相差很大，一個(gè)需要 8000 多邏輯讀，而另一個(gè)只需要 5 個(gè)邏輯讀。觀察二者的執(zhí)行計(jì)劃可以發(fā)現(xiàn)，兩個(gè)執(zhí)行計(jì)劃唯一的區(qū)別就是第二個(gè)查詢在 COUNT

12、 這步使用了 STOPKEY,也就是說，Oracle將 ROWNUMSELECTOBJECT_ID,OBJECT_NAME2 FROM3 (4 SELECTROWNUMRN,OBJECT_ID,OBJECT_NAME5 FROM6 (7 SELECTOBJECT_ID,OBJECT_NAMEFROMT8 )9 WHEREROWNUM=457980;OBJECT_IDOBJECT_NAME7128XCF_I_HANDLE_STATUS7126XCF_P7127XCF_U17142XDF7145XDF_I_DF_KEY7146XDF_I_HANDLE_STATUS7143XDF_P7144XDF_

13、U1TEST.YANGTINGKUNTEST4.YANGTINGKUNYANGTK.YANGTINGKUN 已選擇 11 行。ExecutionPlan0SELECTSTATEMENTOptimizer=CHOOSE(Cost=864Card=457990Bytes=42135080)10VIEW(Cost=864Card=457990Bytes=42135080)21COUNT(STOPKEY)32TABLEACCESS(FULL)OFT(Cost=864Card=457992Bytes=9617832)Statistics0recursivecalls0dbblockgets8979con

14、sistentgets7423physicalreads0redosize680bytessentviaSQL*Nettoclient503bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)11rowsprocessedOracle 分頁查詢語句(三)繼續(xù)看查詢的第二種情況，包含表連接的情況：SQLCREATETABLETASSELECT*FROMDBA_USERS;表已創(chuàng)建。SQLCREATETABLET1ASSELECT*FROMDBA_SOURCE;表已創(chuàng)建。

15、SQLALTERTABLETADDCONSTRAINTPK_TPRIMARYKEY(USERNAME);表已更改。SQLALTERTABLET1ADDCONSTRAINTFK_T1_OWNERFOREIGNKEY(OWNER)2REFERENCEST(USERNAME);表已更改。SQLCREATEINDEXIND_T1_OWNERONT1(NAME);索引已創(chuàng)建。SQLEXECDBMS_STATS.GATHER_TABLE_STATS(USER,T)PL/SQL 過程已成功完成。SQLEXECDBMS_STATS.GATHER_TABLE_STATS(USER,T1)PL/SQL 過程已成功

16、完成。創(chuàng)建了 T 表和 T1 表，默認(rèn)情況下，HASHJOIN 的效率要比 NESTEDLOOP 高很多:SQLSETAUTOTTRACESQLSELECT*FROMT,T1WHERET.USERNAME=T1.OWNER;已選擇 96985 行。ExecutionPlan0SELECTSTATEMENTOptimizer=CHOOSE(Cost=844Card=96985Bytes=46164860)10HASHJOIN(Cost=844Card=96985Bytes=46164860)21TABLEACCESS(FULL)OFT(Cost=2Card=12Bytes=1044)31TABL

17、EACCESS(FULL)OFT1(Cost=826Card=96985Bytes=37727165)Statistics39recursivecalls0dbblockgets14475consistentgets7279physicalreads0redosize37565579bytessentviaSQL*Nettoclient71618bytesreceivedviaSQL*Netfromclient6467SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)96985rowsprocessedSQLSELECT/*+FIR

18、ST_ROWS*/*FROMT,T1WHERET.USERNAME=T1.OWNER;已選擇 96985 行。ExecutionPlan0SELECTSTATEMENTOptimizer=HINT:FIRST_ROWS(Cost=97811Card=96985Bytes=46164860)1 0NESTEDLOOPS(Cost=97811Card=96985Bytes=46164860)21TABLEACCESS(FULL)OFT1(Cost=826Card=96985Bytes=37727165)31TABLEACCESS(BYINDEXROWID)OFT(Cost=1Card=1Bytes

19、=87)43INDEX(UNIQUESCAN)OFPK_T(UNIQUE)Statistics0recursivecalls0dbblockgets117917consistentgets7268physicalreads0redosize37565579bytessentviaSQL*Nettoclient71618bytesreceivedviaSQL*Netfromclient6467SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)96985rowsprocessed但是如果分頁查詢的內(nèi)層是這種連接查詢的話，使用 NESTE

20、DLOOP 可以更快的得到前 N 條記錄下面看一下這種情況下的分頁查詢情況：SQLSELECTUSER_ID,USERNAME,NAME2FROM3(4 SELECTROWNUMRN,USER_ID,USERNAME,NAME5 FROM6 (7 SELECTT.USER_ID,T.USERNAME,T1.NAME8 FROMT,T19 WHERET.USERNAME=T1.OWNER10 )11 WHEREROWNUM=11;已選擇 10 行。ExecutionPlan0SELECTSTATEMENTOptimizer=CHOOSE(Cost=830Card=20Bytes=1200)1 0

21、VIEW(Cost=830Card=20Bytes=1200)2 1COUNT(STOPKEY)3 2HASHJOIN(Cost=830Card=96985Bytes=2909550)43TABLEACCESS(FULL)OFT(Cost=2Card=12Bytes=132)53TABLEACCESS(FULL)OFT1(Cost=826Card=96985Bytes=1842715)Statistics0recursivecalls0dbblockgets8consistentgets7physicalreads0redosize574bytessentviaSQL*Nettoclient5

22、03bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)10rowsprocessed2FROM3 (4 SELECTROWNUMRN,USER_ID,USERNAME,NAME5 FROM6 (7 SELECTT.USER_ID,T.USERNAME,T1.NAME8 FROMT,T19 WHERET.USERNAME=T1.OWNER10 )11 WHEREROWNUM=11;已選擇 10 行。ExecutionPlan0SELECTSTATEMENTOptimi

23、zer=HINT:FIRST_ROWS(Cost=97811Card=20Bytes=1200)1 0VIEW(Cost=97811Card=20Bytes=1200)2 1COUNT(STOPKEY)3 2NESTEDLOOPS(Cost=97811Card=96985Bytes=2909550)43TABLEACCESS(FULL)OFT1(Cost=826Card=96985Bytes=1842715)53TABLEACCESS(BYINDEXROWID)OFT(Cost=1Card=1Bytes=11)65INDEX(UNIQUESCAN)OFPK_T(UNIQUE)Statistic

24、s0recursivecalls0dbblockgets28consistentgets0 physicalreads0 redosize574bytessentviaSQL*Nettoclient503bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)10rowsprocessed看上去似乎 HASHJOIN 效率更高，難道上面說錯(cuò)了。其實(shí)這個(gè)現(xiàn)象是由于這個(gè)例子的特殊性造成的。T 表是根據(jù) DBA_USERS 創(chuàng)建，這張表很小。HASHJOIN中第一步也就是第一張

25、表的全表掃描是無法應(yīng)用 STOPKEY 的，這就是上面提到的 NESTEDLOOP 比 HASHJOIN 優(yōu)勢的地方。但是，這個(gè)例子中，恰好第一張表很小，對這張表的全掃描的代價(jià)極低，因此，顯得 HASHJOIN效率更高。但是，這不具備共性，如果兩張表的大小相近，或者 Oracle 錯(cuò)誤的選擇了先掃描大表，則使用 HASHJOIN 的效率就會低得多。SQLSELECTUSER_ID,USERNAME,NAME2 FROM3 (4 SELECTROWNUMRN,USER_ID,USERNAME,NAME5 FROM6 (7 SELECT/*+ORDERED*/T.USER_ID,T.USERNAM

26、E,T1.NAME8 FROMT1,T9 WHERET.USERNAME=T1.OWNER10 )11 WHEREROWNUM=11;已選擇 10 行。ExecutionPlan0SELECTSTATEMENTOptimizer=CHOOSE(Cost=951Card=20Bytes=1200)1 0VIEW(Cost=951Card=20Bytes=1200)2 1COUNT(STOPKEY)3 2HASHJOIN(Cost=951Card=96985Bytes=2909550)43TABLEACCESS(FULL)OFT1(Cost=826Card=96985Bytes=1842715)5

27、3TABLEACCESS(FULL)OFT(Cost=2Card=12Bytes=132)Statistics0recursivecalls0dbblockgets8585consistentgets7310physicalreads0redosize601bytessentviaSQL*Nettoclient503bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)10rowsprocessed通過 HINT 提示，讓 Oracle 先掃描大表，這回結(jié)果就很明顯了。

28、NESTEDLOOP 的效果要比 HASHJOIN好得多。下面，繼續(xù)比較一下兩個(gè)分頁操作的寫法，為了使結(jié)果更具有代表性，這里都采用了 FIRST_ROWS 提示，讓 Oracle 采用 NESTEDLOOP 的方式來進(jìn)行表連接：SQLSELECT/*+FIRST_ROWS*/USER_ID,USERNAME,NAME2 FROM3 (4 SELECTROWNUMRN,USER_ID,USERNAME,NAME6 (7 SELECTT.USER_ID,T.USERNAME,T1.NAME8 FROMT,T19 WHERET.USERNAME=T1.OWNER10 )11 WHEREROWNUM=

29、11;已選擇 10 行。ExecutionPlan0SELECTSTATEMENTOptimizer=HINT:FIRST_ROWS(Cost=97811Card=20Bytes=1200)1 0VIEW(Cost=97811Card=20Bytes=1200)2 1COUNT(STOPKEY)3 2NESTEDLOOPS(Cost=97811Card=96985Bytes=2909550)43TABLEACCESS(FULL)OFT1(Cost=826Card=96985Bytes=1842715)53TABLEACCESS(BYINDEXROWID)OFT(Cost=1Card=1Byte

30、s=11)65INDEX(UNIQUESCAN)OFPK_T(UNIQUE)Statistics0recursivecalls0dbblockgets28consistentgets0physicalreads0redosize574bytessentviaSQL*Nettoclient503bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)10rowsprocessedSQLSELECT/*+FIRST_ROWS*/USER_ID,USERNAME,NAME2 F

31、ROM3 (4 SELECTROWNUMRN,USER_ID,USERNAME,NAME5 FROM6 (7 SELECTT.USER_ID,T.USERNAME,T1.NAME8 FROMT,T19 WHERET.USERNAME=T1.OWNER10 )12WHERERNBETWEEN11AND20;已選擇 10 行。ExecutionPlan0SELECTSTATEMENTOptimizer=HINT:FIRST_ROWS(Cost=97811Card=96985Bytes=5819100)1 0VIEW(Cost=97811Card=96985Bytes=5819100)2 1COUN

32、T3 2NESTEDLOOPS(Cost=97811Card=96985Bytes=2909550)43TABLEACCESS(FULL)OFT1(Cost=826Card=96985Bytes=1842715)53TABLEACCESS(BYINDEXROWID)OFT(Cost=1Card=1Bytes=11)65INDEX(UNIQUESCAN)OFPK_T(UNIQUE)Statistics0recursivecalls0dbblockgets105571consistentgets7299physicalreads0redosize574bytessentviaSQL*Nettocl

33、ient503bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)10rowsprocessed兩種寫法的效率差別極大。關(guān)鍵仍然是是否能將 STOPKEY 應(yīng)用到最內(nèi)層查詢中。對于表連接來說，在寫分頁查詢的時(shí)候，可以考慮增加 FIRST_ROWS 提示，它有助于更快的將查詢結(jié)果返回。其實(shí)，不光是表連接，對于所有的分頁查詢都可以加上 FIRST_ROWS 提示。不過需要注意的時(shí)，分頁查詢的目標(biāo)是盡快的返回前 N 條記錄，因此，無論是 ROWNUM 還是 FIRST_RO

34、WS 機(jī)制都是提高前幾頁的查詢速度，對于分頁查詢的最后幾頁，采用這些機(jī)制不但無法提高查詢速度，反而會明顯降低查詢效率，對于這一點(diǎn)使用者應(yīng)該做到心中有數(shù)。Oracle 分頁查詢語句(四)最后的例子說明內(nèi)部循環(huán)包含排序的情況：SQLCREATETABLETASSELECT*FROMDBA_OBJECTS;表已創(chuàng)建。SQLCREATEINDEXIND_T_OBJECT_NAMEONT(OBJECT_NAME);索引已創(chuàng)建。SQLALTERTABLETMODIFYOBJECT_NAMENOTNULL;表已更改。SQLEXECDBMS_STATS.GATHER_TABLE_STATS(USER,T)PL

35、/SQL 過程已成功完成。下面進(jìn)行測試包含排序操作的分頁查詢?？梢院唵蔚膶⒉樵兎譃閮煞N不同情況，第一種排序列就是索引列,這種可以利用索引讀取，第二種排序列沒有索引。第一種情況又可以細(xì)分為：完全索引掃描和通過索引掃描定位到表記錄兩種情況。無論是那種情況，都可以通過索引的全掃描來避免排序的產(chǎn)生?？聪旅娴睦樱篠QLSETAUTOTTRACESQLSELECTOBJECT_NAME2 FROM3 (4 SELECTROWNUMRN,OBJECT_NAME5 FROM6 (7 SELECTOBJECT_NAMEFROMTORDERBYOBJECT_NAME8 )9 WHEREROWNUM=11;已選擇

36、 10 行。ExecutionPlan0SELECTSTATEMENTOptimizer=CHOOSE(Cost=26Card=20Bytes=1580)10VIEW(Cost=26Card=20Bytes=1580)21COUNT(STOPKEY)32VIEW(Cost=26Card=6361Bytes=419826)43INDEX(FULLSCAN)OFIND_T_OBJECT_NAME(NON-UNIQUE)(Cost=26Card=6361Bytes=108137)Statistics0recursivecalls0dbblockgets3consistentgets0physical

37、reads0redosize576bytessentviaSQL*Nettoclient503bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)10rowsprocessed這種情況下，通過索引可以完全得到查詢的結(jié)果，因此可以避免表掃描的產(chǎn)生，而且，由于索引已經(jīng)是排序過的，因此通過索引的全掃描，連排序操作都省略了。SQLSELECTOBJECT_ID,OBJECT_NAME3 (4 SELECTROWNUMRN,OBJECT_ID,OBJECT_NAME5 FROM

38、6 (7 SELECTOBJECT_ID,OBJECT_NAMEFROMTORDERBYOBJECT_NAME8 )9 WHEREROWNUM=11;已選擇 10 行。ExecutionPlan0SELECTSTATEMENTOptimizer=CHOOSE(Cost=43Card=20Bytes=1840)10VIEW(Cost=43Card=20Bytes=1840)21COUNT(STOPKEY)32VIEW(Cost=43Card=6361Bytes=502519)43SORT(ORDERBYSTOPKEY)(Cost=43Card=6361Bytes=133581)54TABLEAC

39、CESS(FULL)OFT(Cost=9Card=6361Bytes=133581)Statistics0recursivecalls0dbblockgets81consistentgets0physicalreads0redosize673bytessentviaSQL*Nettoclient503bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient1sorts(memory)0sorts(disk)10rowsprocessed由于不能僅僅通過索引掃描得到查詢結(jié)果，這里 Oracle 選擇了表掃描。這是由于初始化參

40、數(shù)設(shè)置決定的因此，建議在分頁的時(shí)候使用 FIRST_ROWS 提示。SQLSELECT/*+FIRST_ROWS*/OBJECT_ID,OBJECT_NAME2 FROM3 (4 SELECTROWNUMRN,OBJECT_ID,OBJECT_NAME5 FROM6 (7 SELECTOBJECT_ID,OBJECT_NAMEFROMTORDERBYOBJECT_NAME8 )9WHEREROWNUM=11;已選擇 10 行。ExecutionPlan0SELECTSTATEMENTOptimizer=HINT:FIRST_ROWS(Cost=826Card=20Bytes=1840)1 0V

41、IEW(Cost=826Card=20Bytes=1840)2 1COUNT(STOPKEY)3 2VIEW(Cost=826Card=6361Bytes=502519)43TABLEACCESS(BYINDEXROWID)OFT(Cost=826Card=6361Bytes=133581)54INDEX(FULLSCAN)OFIND_T_OBJECT_NAME(NON-UNIQUE)(Cost=26Card=6361)Statistics0recursivecalls0dbblockgets22consistentgets0physicalreads0redosize673bytessent

42、viaSQL*Nettoclient503bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)10rowsprocessed使用了 FIRST_ROWS 提示后，Oracle 不需要掃描全表，而且避免了排序操作。下面討論最后一種情況，排序列不是索引列。這個(gè)時(shí)候排序不可避免，但是利用給出分頁格式,會對所有數(shù)據(jù)進(jìn)行排序，而是只排序前 N 條記錄。SQLSELECTOBJECT_ID,OBJECT_NAME2 FROM3 (4 SELECTROWNUMRN,OBJECT_I

43、D,OBJECT_NAME5 FROM6 (7 SELECTOBJECT_ID,OBJECT_NAMEFROMTORDERBYTIMESTAMP8 )9 )10 WHERERNBETWEEN11AND20;已選擇 10 行。ExecutionPlan0SELECTSTATEMENTOptimizer=CHOOSE(Cost=64Card=6361Bytes=585212)10VIEW(Cost=64Card=6361Bytes=585212)21COUNT32VIEW(Cost=64Card=6361Bytes=502519)Oracle 不43SORT(ORDERBY)(Cost=64Car

44、d=6361Bytes=260801)54TABLEACCESS(FULL)OFT(Cost=9Card=6361Bytes=260801)Statistics0recursivecalls0dbblockgets81consistentgets0physicalreads0redosize690bytessentviaSQL*Nettoclient503bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient1sorts(memory)0sorts(disk)10rowsprocessedSQLSELECTOBJECT_

45、ID,OBJECT_NAME2 FROM3 (4 SELECTROWNUMRN,OBJECT_ID,OBJECT_NAME5 FROM6 (7 SELECTOBJECT_ID,OBJECT_NAMEFROMTORDERBYTIMESTAMP8 )9 WHEREROWNUM=11;已選擇 10 行。ExecutionPlan0SELECTSTATEMENTOptimizer=CHOOSE(Cost=64Card=20Bytes=1840)10VIEW(Cost=64Card=20Bytes=1840)21COUNT(STOPKEY)32VIEW(Cost=64Card=6361Bytes=502

46、519)43SORT(ORDERBYSTOPKEY)(Cost=64Card=6361Bytes=260801)54TABLEACCESS(FULL)OFT(Cost=9Card=6361Bytes=260801)Statistics0recursivecalls0dbblockgets81consistentgets0physicalreads0redosize690bytessentviaSQL*Nettoclient503bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient1sorts(memory)0sorts

47、(disk)10rowsprocessed觀察兩種不同寫法的 ORDERBY 步驟， 一個(gè)是帶 STOPKEY 的 ORDERBY,另一個(gè)不帶。 在大數(shù)據(jù)量需要排序的情況下，帶 STOPKEY 的效率要比不帶 STOPKEY 排序的效率高得多。SQLINSERTINTOTSELECTT.*FROMT,USER_OBJECTS;已創(chuàng)建 407104 行。SQLCOMMIT;提交完成。SQLSELECTOBJECT_ID,OBJECT_NAME2 FROM3 (4 SELECTROWNUMRN,OBJECT_ID,OBJECT_NAMEFROM5 (6 SELECTOBJECT_ID,OBJECT

48、_NAMEFROMTORDERBYTIMESTAMP7 )8 WHEREROWNUM=11;已選擇 10 行。已用時(shí)間：00:00:03.78ExecutionPlan0SELECTSTATEMENTOptimizer=CHOOSE(Cost=64Card=20Bytes=1840)10VIEW(Cost=64Card=20Bytes=1840)21COUNT(STOPKEY)32VIEW(Cost=64Card=6361Bytes=502519)43SORT(ORDERBYSTOPKEY)(Cost=64Card=6361Bytes=260801)54TABLEACCESS(FULL)OFT

49、(Cost=9Card=6361Bytes=260801)Statistics268recursivecalls0dbblockgets6215consistentgets6013physicalreads0redosize740bytessentviaSQL*Nettoclient385bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient6sorts(memory)0sorts(disk)10rowsprocessedSQLSELECTOBJECT_ID,OBJECT_NAME2 FROM3 (4 SELECTROW

50、NUMRN,OBJECT_ID,OBJECT_NAMEFROM5 (6 SELECTOBJECT_ID,OBJECT_NAMEFROMTORDERBYTIMESTAMP7 )8 )9 WHERERNBETWEEN11AND20;已選擇 10 行。已用時(shí)間：00:00:11.86ExecutionPlan0SELECTSTATEMENTOptimizer=CHOOSE(Cost=64Card=6361Bytes=585212)10VIEW(Cost=64Card=6361Bytes=585212)21COUNT32VIEW(Cost=64Card=6361Bytes=502519)43SORT(

51、ORDERBY)(Cost=64Card=6361Bytes=260801)54TABLEACCESS(FULL)OFT(Cost=9Card=6361Bytes=260801)Statistics26recursivecalls12dbblockgets6175consistentgets9219physicalreads0redosize737bytessentviaSQL*Nettoclient385bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient0sorts(memory)1sorts(disk)10row

52、sprocessed觀察兩個(gè)查詢語句的執(zhí)行時(shí)間， 以及統(tǒng)計(jì)信息中的排序信息。 對于第一個(gè)查詢語句， Oracle 利用了 ORDERBYSTOPKEY方式進(jìn)行排序，排序操作只排序需要的 TOPN 的數(shù)據(jù)，因此排序操作放到了內(nèi)存中，而對于第二個(gè)查詢語句來說，進(jìn)行的數(shù)據(jù)的全排序，排序數(shù)據(jù)量大，排序操作不得不在磁盤上完成，因此耗時(shí)比較多。通過上面的例子可以看出給出的標(biāo)準(zhǔn)分頁查詢格式，對于包含排序的操作仍然可以在很大程度上提高分頁查詢性能。Oracle 分頁查詢語句(五)前面的各種例子已經(jīng)說明了分頁查詢語句的標(biāo)準(zhǔn)寫法所帶來的性能提升。這里簡單總結(jié)一下，并簡單的說明分頁查詢語句在何時(shí)無法帶來性能提升。分

53、頁查詢語句之所以可以很快的返回結(jié)果，是因?yàn)樗哪繕?biāo)是最快的返回第一條結(jié)果。如果每頁有 20 條記錄，目前翻到第 5 頁，那么只需要返回前 100 條記錄都可以滿足查詢的要求了，也許還有幾萬條記錄也符合查詢的條件，但是由于分頁的限制，在當(dāng)前的查詢中可以忽略這些數(shù)據(jù)，而只需盡快的返回前 100 條數(shù)據(jù)。這也是為什么在標(biāo)準(zhǔn)分頁查詢語句中經(jīng)常會使用 FIRST_ROWS 提示的原因。對于行操作，可以在得到結(jié)果的同時(shí)將結(jié)果直接返回給上一層調(diào)用。但是對于結(jié)果集操作，Oracle 必須得到結(jié)果集中所有的數(shù)據(jù)，因此分頁查詢中所帶的 ROWNUM 信息不起左右。如果最內(nèi)層的子查詢中包含了下面這些操作中的一個(gè)以上

54、， 則分頁查詢語句無法體現(xiàn)出任何的性能優(yōu)勢： UNIONUNIONALL、 MINUS、 INTERSECT、GROUPBY、DISTINCT、UNIQUE 以及聚集函數(shù)如 MAX、MIN 和分析函數(shù)等。除了這些操作以外，分頁查詢還有一個(gè)很明顯的特點(diǎn)，就是處理的頁數(shù)越小，效率就越高，越到后面，查詢速度越慢。分頁查詢用來提高返回速度的方法都是針對數(shù)據(jù)量較小的前 N 條記錄而言。無論是索引掃描，NESTEDLOOP 連接，還是 ORDERBYSTOPKEY,這些方法帶來性能提升的前提都是數(shù)據(jù)量比較小，一旦分頁到了最后幾頁，會發(fā)現(xiàn)這些方法不但沒有辦法帶來性能的提升，而且性能比普通查詢還要低得多。這一

55、點(diǎn)，在使用分頁查詢的時(shí)候，一定要心里有數(shù)。最后看幾個(gè)例子：首先看看 UNIONALL、GROUPBY 以及分析函數(shù)使外層的 ROWNUM 限制對內(nèi)層查詢無效。SQLSETAUTOTTRACESQLSELECT/*+FIRST_ROWS*/OBJECT_ID,OBJECT_NAME2FROM3 (4 SELECTROWNUMRN,OBJECT_ID,OBJECT_NAME5FROM6 (7 SELECTOBJECT_ID,OBJECT_NAMEFROMTORDERBYOBJECT_NAME8)9 WHEREROWNUM=11;已選擇 10 行。ExecutionPlan0SELECTSTATEM

56、ENTOptimizer=HINT:FIRST_ROWS(Cost=826Card=20Bytes=1840)10VIEW(Cost=826Card=20Bytes=1840)21COUNT(STOPKEY)32VIEW(Cost=826Card=6361Bytes=502519)43TABLEACCESS(BYINDEXROWID)OFT(Cost=826Card=6361Bytes=133581)54INDEX(FULLSCAN)OFIND_T_OBJECT_NAME(NON-UNIQUE)(Cost=26Card=6361)Statistics0recursivecalls0dbbloc

57、kgets23consistentgets0physicalreads0redosize597bytessentviaSQL*Nettoclient503bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)10rowsprocessed這是分頁查詢 ROWNUM 起作用的情況，下面看看如果內(nèi)層查詢包括了集操作時(shí)的情況:SQLSELECT/*+FIRST_ROWS*/OBJECT_ID,OBJECT_NAME2 FROM3 (4 SELECTROWNUMRN,OBJEC

58、T_ID,OBJECT_NAME5 FROM6 (7 SELECTOBJECT_ID,OBJECT_NAMEFROMT8 UNIONALL9 SELECTOBJECT_ID,OBJECT_NAMEFROMT10 ORDERBYOBJECT_NAME11 )12 WHEREROWNUM=11;已選擇 10 行。ExecutionPlan0SELECTSTATEMENTOptimizer=HINT:FIRST_ROWS(Cost=85Card=20Bytes=1840)10VIEW(Cost=85Card=20Bytes=1840)21COUNT(STOPKEY)32VIEW(Cost=85Car

59、d=12722Bytes=1005038)43SORT(ORDERBYSTOPKEY)(Cost=18Card=12722Bytes=267162)54UNION-ALL65TABLEACCESS(FULL)OFT(Cost=9Card=6361Bytes=133581)75TABLEACCESS(FULL)OFT(Cost=9Card=6361Bytes=133581)Statistics0recursivecalls0dbblockgets322consistentgets0physicalreads0redosize546bytessentviaSQL*Nettoclient503byt

60、esreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient1sorts(memory)0sorts(disk)10rowsprocessedSQLSELECT/*+FIRST_ROWS*/OBJECT_ID,OBJECT_NAME2 FROM3 (4 SELECTROWNUMRN,OBJECT_ID,OBJECT_NAME5 FROM6 (7 SELECT/*+INDEX(T)*/OBJECT_ID,OBJECT_NAMEFROMT8 UNIONALL9 SELECT/*+INDEX(T)*/OBJECT_ID,OBJECT_NA