動態(tài)內存表的SQL執(zhí)行計劃

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

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