Oracle分查詢(xún)優(yōu)化

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

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

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

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

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

6、UENCES;表已創(chuàng)建。SQLSELECTCOUNT(*)FROMT;COUNT(*)457992首先比較兩種分頁(yè)方法的區(qū)別：SQLSETAUTOTONSQLCOLOBJECT_NAMEFORMATA30SQLEXECDBMS_STATS.GATHER_TABLE_STATS(USER,T)PL/SQL 過(guò)程已成功完成。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è)邏輯讀。觀(guān)察二者的執(zhí)行計(jì)劃可以發(fā)現(xiàn)，兩個(gè)執(zhí)行計(jì)劃唯一的區(qū)別就是第二個(gè)查詢(xún)?cè)?COUNT

12、 這步使用了 STOPKEY,也就是說(shuō)，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 分頁(yè)查詢(xún)語(yǔ)句(三)繼續(xù)看查詢(xún)的第二種情況，包含表連接的情況：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 過(guò)程已成功完成。SQLEXECDBMS_STATS.GATHER_TABLE_STATS(USER,T1)PL/SQL 過(guò)程已成功

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但是如果分頁(yè)查詢(xún)的內(nèi)層是這種連接查詢(xún)的話(huà)，使用 NESTE

20、DLOOP 可以更快的得到前 N 條記錄下面看一下這種情況下的分頁(yè)查詢(xú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 效率更高，難道上面說(shuō)錯(cuò)了。其實(shí)這個(gè)現(xiàn)象是由于這個(gè)例子的特殊性造成的。T 表是根據(jù) DBA_USERS 創(chuàng)建，這張表很小。HASHJOIN中第一步也就是第一張

25、表的全表掃描是無(wú)法應(yīng)用 STOPKEY 的，這就是上面提到的 NESTEDLOOP 比 HASHJOIN 優(yōu)勢(shì)的地方。但是，這個(gè)例子中，恰好第一張表很小，對(duì)這張表的全掃描的代價(jià)極低，因此，顯得 HASHJOIN效率更高。但是，這不具備共性，如果兩張表的大小相近，或者 Oracle 錯(cuò)誤的選擇了先掃描大表，則使用 HASHJOIN 的效率就會(huì)低得多。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通過(guò) HINT 提示，讓 Oracle 先掃描大表，這回結(jié)果就很明顯了。

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

34、WS 機(jī)制都是提高前幾頁(yè)的查詢(xún)速度，對(duì)于分頁(yè)查詢(xún)的最后幾頁(yè)，采用這些機(jī)制不但無(wú)法提高查詢(xún)速度，反而會(huì)明顯降低查詢(xún)效率，對(duì)于這一點(diǎn)使用者應(yīng)該做到心中有數(shù)。Oracle 分頁(yè)查詢(xún)語(yǔ)句(四)最后的例子說(shuō)明內(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 過(guò)程已成功完成。下面進(jìn)行測(cè)試包含排序操作的分頁(yè)查詢(xún)?？梢院?jiǎn)單的將查詢(xún)分為兩種不同情況，第一種排序列就是索引列,這種可以利用索引讀取，第二種排序列沒(méi)有索引。第一種情況又可以細(xì)分為：完全索引掃描和通過(guò)索引掃描定位到表記錄兩種情況。無(wú)論是那種情況，都可以通過(guò)索引的全掃描來(lái)避免排序的產(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這種情況下，通過(guò)索引可以完全得到查詢(xún)的結(jié)果，因此可以避免表掃描的產(chǎn)生，而且，由于索引已經(jīng)是排序過(guò)的，因此通過(guò)索引的全掃描，連排序操作都省略了。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由于不能僅僅通過(guò)索引掃描得到查詢(xún)結(jié)果，這里 Oracle 選擇了表掃描。這是由于初始化參

40、數(shù)設(shè)置決定的因此，建議在分頁(yè)的時(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í)候排序不可避免，但是利用給出分頁(yè)格式,會(huì)對(duì)所有數(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觀(guān)察兩種不同寫(xiě)法的 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觀(guān)察兩個(gè)查詢(xún)語(yǔ)句的執(zhí)行時(shí)間， 以及統(tǒng)計(jì)信息中的排序信息。 對(duì)于第一個(gè)查詢(xún)語(yǔ)句， Oracle 利用了 ORDERBYSTOPKEY方式進(jìn)行排序，排序操作只排序需要的 TOPN 的數(shù)據(jù)，因此排序操作放到了內(nèi)存中，而對(duì)于第二個(gè)查詢(xún)語(yǔ)句來(lái)說(shuō)，進(jìn)行的數(shù)據(jù)的全排序，排序數(shù)據(jù)量大，排序操作不得不在磁盤(pán)上完成，因此耗時(shí)比較多。通過(guò)上面的例子可以看出給出的標(biāo)準(zhǔn)分頁(yè)查詢(xún)格式，對(duì)于包含排序的操作仍然可以在很大程度上提高分頁(yè)查詢(xún)性能。Oracle 分頁(yè)查詢(xún)語(yǔ)句(五)前面的各種例子已經(jīng)說(shuō)明了分頁(yè)查詢(xún)語(yǔ)句的標(biāo)準(zhǔn)寫(xiě)法所帶來(lái)的性能提升。這里簡(jiǎn)單總結(jié)一下，并簡(jiǎn)單的說(shuō)明分頁(yè)查詢(xún)語(yǔ)句在何時(shí)無(wú)法帶來(lái)性能提升。分

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

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

55、點(diǎn)，在使用分頁(yè)查詢(xún)的時(shí)候，一定要心里有數(shù)。最后看幾個(gè)例子：首先看看 UNIONALL、GROUPBY 以及分析函數(shù)使外層的 ROWNUM 限制對(duì)內(nèi)層查詢(xún)無(wú)效。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這是分頁(yè)查詢(xún) ROWNUM 起作用的情況，下面看看如果內(nèi)層查詢(xún)包括了集操作時(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