動(dòng)態(tài)內(nèi)存表的SQL執(zhí)行計(jì)劃

動(dòng)態(tài)內(nèi)存表的SQL執(zhí)行計(jì)劃術(shù)語(yǔ)解釋動(dòng)態(tài)內(nèi)存表：本文中提到的動(dòng)態(tài)內(nèi)存表，是指PLSQL中的集合類(lèi)型，即一個(gè)單列的二維表，官方文檔稱為Nestedtables，它的定義方式如下所示：CreateOrReplaceTypet_NumListasTableofNumber可以在SQL語(yǔ)句中使用Table函數(shù)將這種類(lèi)型的數(shù)據(jù)轉(zhuǎn)換為表，這個(gè)表有一個(gè)固定字段名：Column_Value。這種表中的數(shù)據(jù)存而在PGA中，是動(dòng)態(tài)的，臨時(shí)的。執(zhí)行計(jì)劃：所謂執(zhí)行計(jì)劃，顧名思義，就是對(duì)一個(gè)查詢?nèi)蝿?wù)，做出一份怎樣去完成任務(wù)的詳細(xì)方案。舉個(gè)生活中的例子，要從重慶去云南，可以走貴陽(yáng)線，可以走宜賓線，每一條路所花的時(shí)間不同，路費(fèi)不同。哪一條路速度最快，路費(fèi)最少，需要根據(jù)一些信息進(jìn)行判斷，從而作出一個(gè)最優(yōu)選擇。Oracle中的優(yōu)化器就是通過(guò)一套判斷策略，確定SQL語(yǔ)句訪問(wèn)和讀取數(shù)據(jù)的最優(yōu)路徑和方法。動(dòng)態(tài)內(nèi)存表的SQL執(zhí)行計(jì)劃f_Num2list是ZLHIS中的一個(gè)自定義函數(shù)，它用于將一串?dāng)?shù)字序列轉(zhuǎn)換為一張動(dòng)態(tài)內(nèi)存表，常見(jiàn)用法如下：SelectColumn_ValueFromTable(f_Num2list('1,2,3,4'));現(xiàn)在，我們來(lái)看它在多表連接中的常見(jiàn)應(yīng)用。SQL1：首先，我們使用主鍵”ID”為主要條件來(lái)查詢”門(mén)診費(fèi)用記錄”：Selecta.NoFrom門(mén)診費(fèi)用記錄A,Table(f_Num2list('1,2,3,4'))BWherea.Id=b.Column_Value;它的執(zhí)行計(jì)劃如下：Description曰SELECTSTA7E1UZNT,b.Column_Value;它的執(zhí)行計(jì)劃如下：Description曰SELECTSTA7E1UZNT,GOAL=ALL-ROWSB-NESTEDLOOPSCOLLECTIONITERATORPICKLERFETCH呂TABLEACCESSBTINDEXROWIDDJDEKUNIQUESCAN對(duì)?斫有者登圭1祐桃困其致164581681388661645S16S13SS56F.IO2LISTYHIS門(mén)音蔓用記錄1j16THIS門(mén)診查用記錄1SQL2：我們改變SQL中的連接條件，使用”醫(yī)囑序號(hào)”這個(gè)條件:Selecta.NOFrom門(mén)診費(fèi)用記錄A,Table(f_Num2list('1,2,3,4'))BWherea.醫(yī)囑序號(hào)=b.Column_Value它的執(zhí)行計(jì)劃如下：Description時(shí)彖名郡學(xué)節(jié)BSELECTSTATEWIT,GOAL=1LL.R0WS164E2...3697...4TABLEACCESSBTIHDEKROTITTHIS1診蕉用記錄13.,.3T2691B-IIESTEDLOOPS16452...3597...COLLECTI0MITERATORPICELERF,…DffiEX皿GESCANTHISii診賈用記錄_口_醫(yī)囑用號(hào)11由上圖可見(jiàn)，仍然使用了嵌套連接，不同的是，用到了索弓1“門(mén)診費(fèi)用記錄_IX_醫(yī)囑序號(hào)”進(jìn)行索引范圍掃描，效率比較高。SQL3：再改變SQL中的連接條件，使用”病人ID”這個(gè)條件：Selecta.NOFrom門(mén)診費(fèi)用記錄A,Table(f_Num2list('1,2,3,4'))BWherea.病人id=b.Column_Value;它的執(zhí)行計(jì)劃如下：—1—尋FDescription同象名稱耗塞蒐數(shù)日SELECTSWEMENLGOAL=^LL_RO￥S31572…3G97...日TEKJ&EJOIN31572...3597.._IJLBLEACCESSEYIlffiEXROWIDTHIS門(mén)診費(fèi)用記錄43D3..?3726...INDEXFIFLLSCAN門(mén)診斐用記錄_氏_京人邛49020a-SORTJOIN12S16810330COLLECTlOJfEFEEiATORPICHLERF,「FNUH2LIST由上圖可見(jiàn)，它使用了排序合并連接，對(duì)索引“門(mén)診費(fèi)用記錄_IX_病人id”進(jìn)行了索引全掃描，效率非常低。SQL4：再改變SQL中的連接條件，使用”收入項(xiàng)目id”這個(gè)條件：Selecta.NOFrom門(mén)診費(fèi)用記錄A,Table(f_Num2list('1,2,3,4'))BWherea.收入項(xiàng)目id=b.Column_Value;它的執(zhí)行計(jì)劃如下：喝mgptMii時(shí)象折有者時(shí)象名稱腿星教.宇節(jié)H-SELECTSTATEHEHLGOAL二ALL_R3^S129154...□HASHJOIN12PL6土…09LLEC7IOJTITERATORPICKLERFETCHF_W2LI3TTABLEACCESSFULLTHIS門(mén)診費(fèi)用記錄8245土…由上圖可見(jiàn)，它使用了全表掃描，沒(méi)有用到任何索引，效率非常差。我們?cè)賮?lái)回顧上述4條SQL語(yǔ)句條件的變化：ID(主鍵)，醫(yī)囑序號(hào)，病人也，收入項(xiàng)目id分析他們的特性可以發(fā)現(xiàn)：前兩個(gè)條件字段對(duì)應(yīng)的索引可選擇性非常高，它們的執(zhí)行計(jì)劃中利用了索引進(jìn)行高效的查詢；第3個(gè)字段”病人ID”的選擇性較低，一個(gè)鍵值存在數(shù)條或數(shù)十條記錄，這些數(shù)據(jù)分散在不同的塊上，使用索引訪問(wèn)數(shù)據(jù)的成本比較高，所以執(zhí)行計(jì)劃選擇了排序合并連接，但卻錯(cuò)誤的選擇了索引全掃描，這個(gè)索引非常大，效率很低。第3個(gè)字段”收入項(xiàng)目id”的選擇性非常差，一個(gè)鍵值存在成千上萬(wàn)條記錄，使用索引訪問(wèn)的成本非常高，所以，執(zhí)行計(jì)劃選擇了全表掃描。綜上所述，動(dòng)態(tài)內(nèi)存表(Table(f_Num2list('1,2,3,4')))是否能夠正常利用索引，優(yōu)化器能否選擇正確的執(zhí)行計(jì)劃，跟表間連接字段對(duì)應(yīng)的索引鍵值的可選擇性密切相關(guān)。動(dòng)態(tài)內(nèi)存表執(zhí)行計(jì)劃異常的原因分析很多時(shí)候我們希望利用索引快速訪問(wèn)數(shù)據(jù)，然而優(yōu)化器選擇的執(zhí)行計(jì)劃卻是性能糟糕的全表掃描，例如下面這句SQL：SelectDistincta.病人id,a.主頁(yè)id,a.診療類(lèi)別，e.操作類(lèi)型,a.執(zhí)行頻次From病人醫(yī)囑記錄A,診療項(xiàng)目目錄E,Table(f_Num2list('1,2,3,4'))BWherea.病人id=b.Column_ValueAnda.診療項(xiàng)目id=e.Id;我們期望優(yōu)化器為我們選擇”病人醫(yī)囑記錄_IX_病人id”這個(gè)索引，通過(guò)表間連接進(jìn)行索引范圍掃描，然而執(zhí)行計(jì)劃卻是這樣:DeacriptLDn對(duì)碧.所有考sa—1LL子FSELECTSTATEMENTGOAL=ALL.EOWS386962EHASHUNIQUE洶…3359625F>-HASHJOIJf2541091..?2988.7ABLE皿ESSFULLYBIS痘入醫(yī)嗔記錄27161..?*136…EF-BERGEIDINCARTESIO1D93T61..?4287.TABLEACCESSFULLYins診療項(xiàng)目目錄1B1..?26246BBUFFERSORT1D9359916816336'roLLECIIOUirERATOEPICE...F-NTOISLIST由上圖可見(jiàn)，大表全表掃描這樣糟糕的執(zhí)行計(jì)劃不是我們想要的，是什么原因?qū)е聝?yōu)化器選擇了錯(cuò)誤的訪問(wèn)路徑呢？查閱相關(guān)資料得知，原來(lái)動(dòng)態(tài)內(nèi)存表(Table(f_Num2list('1,2,3,4')))實(shí)際上是一個(gè)函數(shù)轉(zhuǎn)換后的結(jié)果集，其中的記錄數(shù)是動(dòng)態(tài)的，在運(yùn)行時(shí)才知道結(jié)果集的記錄數(shù)，而優(yōu)化器評(píng)估訪問(wèn)路徑期間無(wú)法得知Table函數(shù)結(jié)果集的記錄數(shù)，也就無(wú)法得知它的成本，于是就采用了一個(gè)固定的假設(shè)：假設(shè)會(huì)返回8168條記錄，并根據(jù)這個(gè)數(shù)據(jù)來(lái)評(píng)估成本。從上圖可看到，基數(shù)這列在最后一行的數(shù)字正好就是8168，而實(shí)際上我們只有4行數(shù)據(jù)返回，正是由于這個(gè)錯(cuò)誤的假設(shè)，與實(shí)際行數(shù)如此大的偏差，導(dǎo)致優(yōu)化器選擇了錯(cuò)誤的執(zhí)行計(jì)劃：哈希連接和全表掃描。通過(guò)Hints改變動(dòng)態(tài)內(nèi)存表的執(zhí)行計(jì)劃既然優(yōu)化器無(wú)法做出正確的成本評(píng)估，無(wú)法選擇正確的執(zhí)行計(jì)劃，有哪些辦法可以幫助優(yōu)化器改正這個(gè)錯(cuò)誤呢？下面介紹三種方法。第一種：利用基于規(guī)則的優(yōu)化器來(lái)固定執(zhí)行計(jì)劃優(yōu)化器有兩種模式來(lái)選擇執(zhí)行計(jì)劃：基于規(guī)則和基于成本。即然是成本評(píng)估出了錯(cuò)，我們就不讓它評(píng)估成本好了，指定基于規(guī)則的優(yōu)化器模式，它會(huì)按照Oracle內(nèi)核中一套固定規(guī)則來(lái)決定表連接方式和訪問(wèn)順序。同樣是上面的SQL，我們?cè)谄渲屑尤搿盎谝?guī)則的優(yōu)化器模式”提示符：

Select/*+rule*/Distincta.病人id,a.主頁(yè)id,a.診療類(lèi)別，e.操作類(lèi)型,a.執(zhí)行頻次From病人醫(yī)囑記錄A,診療項(xiàng)目目錄E,Table(f_Num2list('1,2,3,4'))BWherea.病人id=b.Column_ValueAnda.診療項(xiàng)目id=e.Id;主要是紅色部分，增加了一個(gè)提示字rule，指示優(yōu)化器基于規(guī)則來(lái)制定數(shù)據(jù)訪問(wèn)路徑和方法。現(xiàn)在SQL的執(zhí)行計(jì)劃如下：撫至其數(shù)字節(jié)F_NU1T2LISIJRAEffi撫至其數(shù)字節(jié)F_NU1T2LISIJRAEffi記錄病人醫(yī)矚記錄_亦_主如D慘療項(xiàng)目目錄診療頊目巨錄_PE曰SELECTSTATEJEtfTjGOAL二HTMT1:RULE=i-SQRIIIMIQUEFBESTEDLCOPSBNESTEDLOOPSCOLLECTZdWITEEAIORFICZLEEFETCH□TABLEACCESSBYIMDEXROTOINDEXRANGESCAN3TABLEACCESSBYINDEXROVIDIHDEXUNIQUESCAN可以看到，使用了正確的索引”病人醫(yī)囑記錄—IX—主頁(yè)ID”，數(shù)據(jù)訪問(wèn)效率較高。這種方式的弊端就是基于規(guī)則的優(yōu)化器，Oracle已經(jīng)明確停止對(duì)它的技術(shù)支持，新的特性也不再支持，例如：分區(qū)，如果表是分區(qū)的，rule提示字將自動(dòng)失效，優(yōu)化器會(huì)采用基于成本評(píng)估模式，這種情況，仍然會(huì)選擇錯(cuò)誤的執(zhí)行計(jì)劃。第二種：指定動(dòng)態(tài)內(nèi)存表的行數(shù)即然優(yōu)化器假設(shè)的8168不是我們想要的，那我們就指定一個(gè)正確的值，幫助優(yōu)化器來(lái)確定動(dòng)態(tài)內(nèi)存表的行數(shù)，從而做出正確的評(píng)估，SQL如下：Select/*+CARDINALITY(b4)*/Distincta.病人id,a.主頁(yè)id,a.診療類(lèi)別,e.操作類(lèi)型,a.執(zhí)行頻次From病人醫(yī)囑記錄A,診療項(xiàng)目目錄E,Table(f_Num2list('1,2,3,4'))BWherea.病人id=b.Column_ValueAnda.診療項(xiàng)目id=e.Id;紅色部分，增加了一個(gè)提示字CARDINALITY(b4)，向優(yōu)化器說(shuō)明動(dòng)態(tài)內(nèi)存表b表只有4行數(shù)據(jù)?，F(xiàn)在SQL的執(zhí)行計(jì)劃如下：Description對(duì)懸宅稱擇喪其致享節(jié)ESELECTSTATEHEHTjCDAL=ALL_ROVS5038613476古HASHUNIQUE50305134753-HASHJODI4944615610E-TJ1ELEACCESSBYINDEXEflUIDYHIS肩攻匡蜻記錄5112如12HNESTEDLCOPS3144612488COLLECIIDNITERAMRPICELERF_!Ofl2LISTIJJIiEXKANGE丈期THIS孺人匡囑記錄_皿_主頁(yè)卬1J12TAELEACCESSFULLTHIS診療項(xiàng)呂目錄181…91861可以看到，使用了正確的索引”病人醫(yī)囑記錄_IX_±頁(yè)ID”，但不太完美的是使用了哈希連接，對(duì)”診療項(xiàng)目目錄”進(jìn)行了全表掃描，這不是我們希望看到的，它的執(zhí)行效率還需要進(jìn)一步優(yōu)化。第三種：指定連接方式和順序即然優(yōu)化器不能對(duì)表的訪問(wèn)方式和順序做出正確的選擇，那就讓我們自己指定吧。Select/*+leading(b)use_nl(a,e)*/Distincta.病人id,a.主頁(yè)id,a.診療類(lèi)別,e.操作類(lèi)型,a.執(zhí)行頻次From病人醫(yī)囑記錄A,診療項(xiàng)目目錄E,Table(f_Num2list('1,2,3,4'))BWherea.病人id=b.Column_ValueAnda.診療項(xiàng)目id=e.Id;紅色部分，增加了一個(gè)提示字/*+leading(b)use_nl(a,e向優(yōu)化器指明，優(yōu)先訪問(wèn)b表，對(duì)表a和e采用嵌套連接?，F(xiàn)在SQL的執(zhí)行計(jì)劃如下：耗離其致季節(jié)Description對(duì)景所有者Q-SELECTSrkTEMENT,COAL=ALL_ROWS22^92538513475E}-HASHUJfIQUE22^92538513475E-NESTEDLO3PS22^8^39…318?…曰1IE3TEDLOOPS425299…255CL..OOLLECTIOnITERATORPICELERFETCHF_EIIK7LI^r白TABLEACCESSBYIHDEKROWIDVHZS病人醫(yī)爆記錄G1122912riTOEXWIGESCANTHIS漏人醫(yī)屈記錄_u_主頁(yè)邛]U2S7疝LEACCESSBYINDEKROVID診療項(xiàng)目目錄117INDEXEKIQEESCANTHIS診療垣目泊錄一FE11從上圖可以看到，執(zhí)行計(jì)劃中使用了正確的索引”病人醫(yī)囑記錄_IX_主頁(yè)ID”和”診療項(xiàng)目目錄