动态内存表的SQL执行计划

动态内存表的SQL执行计划术语解释动态内存表：本文中提到的动态内存表，是指PLSQL中的集合类型，即一个单列的二维表，官方文档称为Nestedtables，它的定义方式如下所示：CreateOrReplaceTypet_NumListasTableofNumber可以在SQL语句中使用Table函数将这种类型的数据转换为表，这个表有一个固定字段名：Column_Value。这种表中的数据存而在PGA中，是动态的，临时的。执行计划：所谓执行计划，顾名思义，就是对一个查询任务，做出一份怎样去完成任务的详细方案。举个生活中的例子，要从重庆去云南，可以走贵阳线，可以走宜宾线，每一条路所花的时间不同，路费不同。哪一条路速度最快，路费最少，需要根据一些信息进行判断，从而作出一个最优选择。Oracle中的优化器就是通过一套判断策略，确定SQL语句访问和读取数据的最优路径和方法。动态内存表的SQL执行计划f_Num2list是ZLHIS中的一个自定义函数，它用于将一串数字序列转换为一张动态内存表，常见用法如下：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;它的执行计划如下：Description曰SELECTSTA7E1UZNT,b.Column_Value;它的执行计划如下：Description曰SELECTSTA7E1UZNT,GOAL=ALL-ROWSB-NESTEDLOOPSCOLLECTIONITERATORPICKLERFETCH吕TABLEACCESSBTINDEXROWIDDJDEKUNIQUESCAN对•斫有者登圭1祐桃困其致164581681388661645S16S13SS56F.IO2LISTYHIS门音蔓用记录1j16THIS门诊查用记录1SQL2：我们改变SQL中的连接条件，使用”医嘱序号”这个条件:Selecta.NOFrom门诊费用记录A,Table(f_Num2list('1,2,3,4'))BWherea.医嘱序号=b.Column_Value它的执行计划如下：Description时彖名郡学节BSELECTSTATEWIT,GOAL=1LL.R0WS164E2...3697...4TABLEACCESSBTIHDEKROTITTHIS1诊蕉用记录13.,.3T2691B-IIESTEDLOOPS16452...3597...COLLECTI0MITERATORPICELERF,…DffiEX皿GESCANTHISii诊贾用记录_口_医嘱用号11由上图可见，仍然使用了嵌套连接，不同的是，用到了索弓1“门诊费用记录_IX_医嘱序号”进行索引范围扫描，效率比较高。SQL3：再改变SQL中的连接条件，使用”病人ID”这个条件：Selecta.NOFrom门诊费用记录A,Table(f_Num2list('1,2,3,4'))BWherea.病人id=b.Column_Value;它的执行计划如下：—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;它的执行计划如下：喝mgptMii时象折有者时象名称腿星教.宇节H-SELECTSTATEHEHLGOAL二ALL_R3^S129154...□HASHJOIN12PL6土…09LLEC7IOJTITERATORPICKLERFETCHF_W2LI3TTABLEACCESSFULLTHIS门诊费用记录8245土…由上图可见，它使用了全表扫描，没有用到任何索引，效率非常差。我们再来回顾上述4条SQL语句条件的变化：ID(主键)，医嘱序号，病人也，收入项目id分析他们的特性可以发现：前两个条件字段对应的索引可选择性非常高，它们的执行计划中利用了索引进行高效的查询；第3个字段”病人ID”的选择性较低，一个键值存在数条或数十条记录，这些数据分散在不同的块上，使用索引访问数据的成本比较高，所以执行计划选择了排序合并连接，但却错误的选择了索引全扫描，这个索引非常大，效率很低。第3个字段”收入项目id”的选择性非常差，一个键值存在成千上万条记录，使用索引访问的成本非常高，所以，执行计划选择了全表扫描。综上所述，动态内存表(Table(f_Num2list('1,2,3,4')))是否能够正常利用索引，优化器能否选择正确的执行计划，跟表间连接字段对应的索引键值的可选择性密切相关。动态内存表执行计划异常的原因分析很多时候我们希望利用索引快速访问数据，然而优化器选择的执行计划却是性能糟糕的全表扫描，例如下面这句SQL：SelectDistincta.病人id,a.主页id,a.诊疗类别，e.操作类型,a.执行频次From病人医嘱记录A,诊疗项目目录E,Table(f_Num2list('1,2,3,4'))BWherea.病人id=b.Column_ValueAnda.诊疗项目id=e.Id;我们期望优化器为我们选择”病人医嘱记录_IX_病人id”这个索引，通过表间连接进行索引范围扫描，然而执行计划却是这样:DeacriptLDn对碧.所有考sa—1LL子FSELECTSTATEMENTGOAL=ALL.EOWS386962EHASHUNIQUE汹…3359625F>-HASHJOIJf2541091..・2988.7ABLE皿ESSFULLYBIS痘入医嗔记录27161..・*136…EF-BERGEIDINCARTESIO1D93T61..・4287.TABLEACCESSFULLYins诊疗项目目录1B1..・26246BBUFFERSORT1D9359916816336'roLLECIIOUirERATOEPICE...F-NTOISLIST由上图可见，大表全表扫描这样糟糕的执行计划不是我们想要的，是什么原因导致优化器选择了错误的访问路径呢？查阅相关资料得知，原来动态内存表(Table(f_Num2list('1,2,3,4')))实际上是一个函数转换后的结果集，其中的记录数是动态的，在运行时才知道结果集的记录数，而优化器评估访问路径期间无法得知Table函数结果集的记录数，也就无法得知它的成本，于是就采用了一个固定的假设：假设会返回8168条记录，并根据这个数据来评估成本。从上图可看到，基数这列在最后一行的数字正好就是8168，而实际上我们只有4行数据返回，正是由于这个错误的假设，与实际行数如此大的偏差，导致优化器选择了错误的执行计划：哈希连接和全表扫描。通过Hints改变动态内存表的执行计划既然优化器无法做出正确的成本评估，无法选择正确的执行计划，有哪些办法可以帮助优化器改正这个错误呢？下面介绍三种方法。第一种：利用基于规则的优化器来固定执行计划优化器有两种模式来选择执行计划：基于规则和基于成本。即然是成本评估出了错，我们就不让它评估成本好了，指定基于规则的优化器模式，它会按照Oracle内核中一套固定规则来决定表连接方式和访问顺序。同样是上面的SQL，我们在其中加入“基于规则的优化器模式”提示符：

Select/*+rule*/Distincta.病人id,a.主页id,a.诊疗类别，e.操作类型,a.执行频次From病人医嘱记录A,诊疗项目目录E,Table(f_Num2list('1,2,3,4'))BWherea.病人id=b.Column_ValueAnda.诊疗项目id=e.Id;主要是红色部分，增加了一个提示字rule，指示优化器基于规则来制定数据访问路径和方法。现在SQL的执行计划如下：抚至其数字节F_NU1T2LISIJRAEffi抚至其数字节F_NU1T2LISIJRAEffi记录病人医瞩记录_亦_主如D惨疗项目目录诊疗顼目巨录_PE曰SELECTSTATEJEtfTjGOAL二HTMT1:RULE=i-SQRIIIMIQUEFBESTEDLCOPSBNESTEDLOOPSCOLLECTZdWITEEAIORFICZLEEFETCH□TABLEACCESSBYIMDEXROTOINDEXRANGESCAN3TABLEACCESSBYINDEXROVIDIHDEXUNIQUESCAN可以看到，使用了正确的索引”病人医嘱记录—IX—主页ID”，数据访问效率较高。这种方式的弊端就是基于规则的优化器，Oracle已经明确停止对它的技术支持，新的特性也不再支持，例如：分区，如果表是分区的，rule提示字将自动失效，优化器会采用基于成本评估模式，这种情况，仍然会选择错误的执行计划。第二种：指定动态内存表的行数即然优化器假设的8168不是我们想要的，那我们就指定一个正确的值，帮助优化器来确定动态内存表的行数，从而做出正确的评估，SQL如下：Select/*+CARDINALITY(b4)*/Distincta.病人id,a.主页id,a.诊疗类别,e.操作类型,a.执行频次From病人医嘱记录A,诊疗项目目录E,Table(f_Num2list('1,2,3,4'))BWherea.病人id=b.Column_ValueAnda.诊疗项目id=e.Id;红色部分，增加了一个提示字CARDINALITY(b4)，向优化器说明动态内存表b表只有4行数据。现在SQL的执行计划如下：Description对悬宅称择丧其致享节ESELECTSTATEHEHTjCDAL=ALL_ROVS5038613476古HASHUNIQUE50305134753-HASHJODI4944615610E-TJ1ELEACCESSBYINDEXEflUIDYHIS肩攻匡蜻记录5112如12HNESTEDLCOPS3144612488COLLECIIDNITERAMRPICELERF_!Ofl2LISTIJJIiEXKANGE丈期THIS孺人匡嘱记录_皿_主页卬1J12TAELEACCESSFULLTHIS诊疗项吕目录181…91861可以看到，使用了正确的索引”病人医嘱记录_IX_±页ID”，但不太完美的是使用了哈希连接，对”诊疗项目目录”进行了全表扫描，这不是我们希望看到的，它的执行效率还需要进一步优化。第三种：指定连接方式和顺序即然优化器不能对表的访问方式和顺序做出正确的选择，那就让我们自己指定吧。Select/*+leading(b)use_nl(a,e)*/Distincta.病人id,a.主页id,a.诊疗类别,e.操作类型,a.执行频次From病人医嘱记录A,诊疗项目目录E,Table(f_Num2list('1,2,3,4'))BWherea.病人id=b.Column_ValueAnda.诊疗项目id=e.Id;红色部分，增加了一个提示字/*+leading(b)use_nl(a,e向优化器指明，优先访问b表，对表a和e采用嵌套连接。现在SQL的执行计划如下：耗离其致季节Description对景所有者Q-SELECTSrkTEMENT,COAL=ALL_ROWS22^92538513475E}-HASHUJfIQUE22^92538513475E-NESTEDLO3PS22^8^39…318?…曰1IE3TEDLOOPS425299…255CL..OOLLECTIOnITERATORPICELERFETCHF_EIIK7LI^r白TABLEACCESSBYIHDEKROWIDVHZS病人医爆记录G1122912riTOEXWIGESCANTHIS漏人医屈记录_u_主页邛]U2S7疝LEACCESSBYINDEKROVID诊疗项目目录117INDEXEKIQEESCANTHIS诊疗垣目泊录一FE11从上图可以看到，执行计划中使用了正确的索引”病人医嘱记录_IX_主页ID”和”诊疗项目目录