Oracle分查詢優(yōu)化

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

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

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

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

5、對于分頁查詢選擇 NESTEDLOOP 作為查詢的連接方法具有較高的效率（分頁查詢的時候絕大部分的情況是查詢前幾頁的數據，越靠后面的頁數訪問幾率越?。?。因此，如果不介意在系統(tǒng)中使用 HINT 的話，可以將分頁的查詢語句改寫為：SELECT/*+FIRST_ROWS*/*FROM(SELECTA.*,ROWNUMRNFROM(SELECT*FROMTABLE_NAME)AWHEREROWNUM=21Oracle 分頁查詢語句(二)這篇文章用幾個例子來說明分頁查詢的效率。首先構造一個比較大的表作為測試表：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í)行效率相差很大，一個需要 8000 多邏輯讀，而另一個只需要 5 個邏輯讀。觀察二者的執(zhí)行計劃可以發(fā)現，兩個執(zhí)行計劃唯一的區(qū)別就是第二個查詢在 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 表，默認情況下，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但是如果分頁查詢的內層是這種連接查詢的話，使用 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 效率更高，難道上面說錯了。其實這個現象是由于這個例子的特殊性造成的。T 表是根據 DBA_USERS 創(chuàng)建，這張表很小。HASHJOIN中第一步也就是第一張

25、表的全表掃描是無法應用 STOPKEY 的，這就是上面提到的 NESTEDLOOP 比 HASHJOIN 優(yōu)勢的地方。但是，這個例子中，恰好第一張表很小，對這張表的全掃描的代價極低，因此，顯得 HASHJOIN效率更高。但是，這不具備共性，如果兩張表的大小相近，或者 Oracle 錯誤的選擇了先掃描大表，則使用 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 先掃描大表，這回結果就很明顯了。

28、NESTEDLOOP 的效果要比 HASHJOIN好得多。下面，繼續(xù)比較一下兩個分頁操作的寫法，為了使結果更具有代表性，這里都采用了 FIRST_ROWS 提示，讓 Oracle 采用 NESTEDLOOP 的方式來進行表連接：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兩種寫法的效率差別極大。關鍵仍然是是否能將 STOPKEY 應用到最內層查詢中。對于表連接來說，在寫分頁查詢的時候，可以考慮增加 FIRST_ROWS 提示，它有助于更快的將查詢結果返回。其實，不光是表連接，對于所有的分頁查詢都可以加上 FIRST_ROWS 提示。不過需要注意的時，分頁查詢的目標是盡快的返回前 N 條記錄，因此，無論是 ROWNUM 還是 FIRST_RO

34、WS 機制都是提高前幾頁的查詢速度，對于分頁查詢的最后幾頁，采用這些機制不但無法提高查詢速度，反而會明顯降低查詢效率，對于這一點使用者應該做到心中有數。Oracle 分頁查詢語句(四)最后的例子說明內部循環(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 過程已成功完成。下面進行測試包含排序操作的分頁查詢?？梢院唵蔚膶⒉樵兎譃閮煞N不同情況，第一種排序列就是索引列,這種可以利用索引讀取，第二種排序列沒有索引。第一種情況又可以細分為：完全索引掃描和通過索引掃描定位到表記錄兩種情況。無論是那種情況，都可以通過索引的全掃描來避免排序的產生。看下面的例子：SQLSETAUTOTTRACESQLSELECTOBJECT_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這種情況下，通過索引可以完全得到查詢的結果，因此可以避免表掃描的產生，而且，由于索引已經是排序過的，因此通過索引的全掃描，連排序操作都省略了。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由于不能僅僅通過索引掃描得到查詢結果，這里 Oracle 選擇了表掃描。這是由于初始化參

40、數設置決定的因此，建議在分頁的時候使用 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 不需要掃描全表，而且避免了排序操作。下面討論最后一種情況，排序列不是索引列。這個時候排序不可避免，但是利用給出分頁格式,會對所有數據進行排序，而是只排序前 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 步驟， 一個是帶 STOPKEY 的 ORDERBY,另一個不帶。 在大數據量需要排序的情況下，帶 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 行。已用時間：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 行。已用時間：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觀察兩個查詢語句的執(zhí)行時間， 以及統(tǒng)計信息中的排序信息。 對于第一個查詢語句， Oracle 利用了 ORDERBYSTOPKEY方式進行排序，排序操作只排序需要的 TOPN 的數據，因此排序操作放到了內存中，而對于第二個查詢語句來說，進行的數據的全排序，排序數據量大，排序操作不得不在磁盤上完成，因此耗時比較多。通過上面的例子可以看出給出的標準分頁查詢格式，對于包含排序的操作仍然可以在很大程度上提高分頁查詢性能。Oracle 分頁查詢語句(五)前面的各種例子已經說明了分頁查詢語句的標準寫法所帶來的性能提升。這里簡單總結一下，并簡單的說明分頁查詢語句在何時無法帶來性能提升。分

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

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

55、點，在使用分頁查詢的時候，一定要心里有數。最后看幾個例子：首先看看 UNIONALL、GROUPBY 以及分析函數使外層的 ROWNUM 限制對內層查詢無效。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 起作用的情況，下面看看如果內層查詢包括了集操作時的情況: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