本篇内容介绍了“如何解决索引扫描时对同一个叶子块访问多次的问题”的有关知识,在实际案例的操作过程中,不少人都会遇到这样的困境,接下来就让小编带领大家学习一下如何处理这些情况吧!希望大家仔细阅读,能够学有所成!

先创建以下测试环境,以重现相关现象。创建测试表,其中C1列为CHAR(256),目的是使该列占用字节数较多,使得后面在该列上创建索引时,可以用较少的行数构建出2层的索引。

SQL>createtabletest0429(idnumber,c1char(256),v1varchar2(256));Tablecreated.

C1中插入的值为‘01’+254个空格,‘02’+254个空格…这样的值。

SQL>insertintotest0429selectrownumid,lpad(rownum,2,'0')c1,rownumv1fromdualconnectbyrownum<=50;50rowscreated.SQL>commit;Commitcomplete.

在C1列上创建索引:

SQL>createindexind_test0429_c1ontest0429(c1);Indexcreated.

查询该索引的OBJECT_ID,以便查看其树形结构。

SQL>selectobject_id,object_name,object_typefromuser_objectswhereobject_name='IND_TEST0429_C1';OBJECT_IDOBJECT_NAMEOBJECT_TYPE----------------------------------------------------------------97504IND_TEST0429_C1INDEXSQL>altersessionsetevents'immediatetracenametreedumplevel97504';Sessionaltered.SQL>select*fromv$diag_info;INST_IDNAME--------------------------------------------------------------------------VALUE--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------1DiagEnabledTRUE1ADRBase/oradata/app/oracle1ADRHome/oradata/app/oracle/diag/rdbms/orcl/orcl1DiagTrace/oradata/app/oracle/diag/rdbms/orcl/orcl/trace1DiagAlert/oradata/app/oracle/diag/rdbms/orcl/orcl/alert1DiagIncident/oradata/app/oracle/diag/rdbms/orcl/orcl/incident1DiagCdump/oradata/app/oracle/diag/rdbms/orcl/orcl/cdump1HealthMonitor/oradata/app/oracle/diag/rdbms/orcl/orcl/hm1DefaultTraceFile/oradata/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2751.trc1ActiveProblemCount51ActiveIncidentCount1711rowsselected.

在对应的跟踪文件中,看到的索引结构为1个根节点,2个叶子节点。如下所示:

-----begintreedumpbranch:0x180414b25182539(0:nrow:2,level:1)leaf:0x180414c25182540(-1:nrow:26rrow:26)leaf:0x180414d25182541(0:nrow:24rrow:24)-----endtreedump

查询根节点和最左侧叶子节点的数据块所在文件块及块号,准备DUMP其数据块,以便查看其中的内容。

SQL>selectDBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(to_number('&&p3_value','xxxxxxxx'))FILE#,DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(to_number('&&p3_value','xxxxxxxx'))BLOCK#fromdual;23Entervalueforp3_value:180414bold1:selectDBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(to_number('&&p3_value','xxxxxxxx'))FILE#,new1:selectDBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(to_number('180414b','xxxxxxxx'))FILE#,old2:DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(to_number('&&p3_value','xxxxxxxx'))BLOCK#new2:DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(to_number('180414b','xxxxxxxx'))BLOCK#FILE#BLOCK#--------------------616715SQL>undefinep3_valueSQL>selectDBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(to_number('&&p3_value','xxxxxxxx'))FILE#,DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(to_number('&&p3_value','xxxxxxxx'))BLOCK#fromdual;23Entervalueforp3_value:180414cold1:selectDBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(to_number('&&p3_value','xxxxxxxx'))FILE#,new1:selectDBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(to_number('180414c','xxxxxxxx'))FILE#,old2:DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(to_number('&&p3_value','xxxxxxxx'))BLOCK#new2:DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(to_number('180414c','xxxxxxxx'))BLOCK#FILE#BLOCK#--------------------616716

DUMP根块和最左侧叶子块中的内容到跟踪文件中。

SQL>altersystemdumpdatafile6blockmin16715blockmax16716;Systemaltered.

从跟踪文件中,可以看到根块中的主要内容如下所示(为节省篇幅,以下只列出与本主题相关的主要内容,以下其它类似内容亦做了相关处理,不再重复说明):

kdxcolev1KDXCOLEVFlags=---kdxcolok0kdxcoopc0x80:opcode=0:iotflags=---isconverted=Ykdxconco2kdxcosdc0kdxconro1kdxcofbo30=0x1ekdxcofeo8048=0x1f70kdxcoavs8018kdxbrlmc25182540=0x180414ckdxbrsno0kdxbrbksz8056kdxbr2urrc3row#0[8048]dba:25182541=0x180414dcol0;len2;(2):3237col1;TERM-----endofbranchblockdump-----

从上面的倒数第三行的内容中可知,最右侧的叶子块中的最小索引键值为&lsquo;27&rsquo;+254个空格。

从跟踪文件中,可以看到最左侧叶子块中的主要内容如下所示:

kdxcolev0KDXCOLEVFlags=---kdxcolok0kdxcoopc0x80:opcode=0:iotflags=---isconverted=Ykdxconco2kdxcosdc0kdxconro26kdxcofbo88=0x58kdxcofeo1090=0x442kdxcoavs1002kdxlespl0kdxlende0kdxlenxt25182541=0x180414dkdxleprv0=0x0kdxledsz0kdxlebksz8032row#0[7765]flag:------,lock:0,len=267col0;len256;(256):30312020202020202020202020202020202020202020202020......20202020202020202020202020202020202020202020202020202020202020col1;len6;(6):018041470000row#1[7498]flag:------,lock:0,len=267col0;len256;(256):30322020202020202020202020202020202020202020202020......row#25[1090]flag:------,lock:0,len=267col0;len256;(256):32362020202020202020202020202020202020202020202020......20202020202020202020202020202020202020202020202020202020202020col1;len6;(6):018041470019-----endofleafblockdump-----Enddumpdatablockstsn:7file#:6minblk16715maxblk16716

为跟踪索引数据块被访问的情况,打开10200跟踪事件。

SQL>altersessionsetevents'10200tracenamecontextforever,level1';Sessionaltered.

查询位于最左侧叶子块中的数据,由于是等值查询,且C1列上无重复值,故以下查询会返回1行。

SQL>setlines200pages60SQL>selectc1fromtest0429wherec1='01';C1--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------01

由于我们在C1列上创建的索引不是唯一索引,所以此时,对索引的访问方法为索引范围扫描。如下图所示:

SQL>select*fromtable(dbms_xplan.display_cursor('','','typical'));PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID3kt1uqh383qbx,childnumber0-------------------------------------selectc1fromtest0429wherec1='01'Planhashvalue:1267036809------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|------------------------------------------------------------------------------------|0|SELECTSTATEMENT||||1(100)|||*1|INDEXRANGESCAN|IND_TEST0429_C1|1|257|1(0)|00:00:01|------------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------1-access("C1"='01')18rowsselected.

查看10200跟踪文件中的输出,我们可以看到先访问了索引根块,然后访问了最左侧的叶子块。这是符合预期的。但我们可以看到,最左侧的叶子块访问了2次。

ktrgtc2():startedforblock<0x0007:0x0180414b>objd:0x00017ce0ktrget2():startedforblock<0x0007:0x0180414c>objd:0x00017ce0ktrget2():startedforblock<0x0007:0x0180414c>objd:0x00017ce0

之所以被访问两次,我认为其过程如下:

鸿蒙官方战略合作共建——HarmonyOS技术社区

访问索引根块,即访问“block <0x0007 : 0x0180414b>”;

由于条件值&lsquo;01&rsquo;小于根块中,指向第二个叶子块的索引条目中的值&lsquo;27&rsquo;,所以,需要访问索引最左侧的叶子块,即访问“<0x0007 : 0x0180414c>”;

在最左侧的叶子块中找到了第一行满足条件的记录ROW0。暂停继续扫描,而将第一行返回;

继续在最左侧的叶子块中查找是否有满足条件的记录。所以,会再次访问最左侧的叶子块;

在访问ROW1时,得到了值&lsquo;02&rsquo;+254个空格,该值大于&lsquo;01&rsquo;,故整个索引中已不会再有满足条件的记录,所以,结束扫描,退出;

如果在叶子块的扫描中,还能继续找到满足条件值的记录,就不是每找到一行,就暂停扫描并返回当前结果了,而是根据ARRAYSIZE中的值,每凑够该参数指定的行数,才会暂停扫描并返回结果,然后再继续扫描。当发生“再继续扫描”这个动作时,相应的叶子块会被再一次访问。

针对6中所述,我们进行如下测试。将ARRAYSIZE设置为3,即每凑够3行即暂停扫描,返回结果。而该参数的默认值为15。

SQL>showarraysizearraysize15SQL>setarraysize3SQL>showarraysizearraysize3

执行以下查询,应该返回2行。

SQL>selectc1fromtest0429wherec1<='02';C1--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------0102

其对数据块的访问情况如下:

ktrgtc2():startedforblock<0x0007:0x0180414b>objd:0x00017ce0ktrget2():startedforblock<0x0007:0x0180414c>objd:0x00017ce0ktrget2():startedforblock<0x0007:0x0180414c>objd:0x00017ce0

执行以下查询,会返回3行。

SQL>selectc1fromtest0429wherec1<='03';C1--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------010203

其对数据块的访问情况如下:

ktrgtc2():startedforblock<0x0007:0x0180414b>objd:0x00017ce0ktrget2():startedforblock<0x0007:0x0180414c>objd:0x00017ce0ktrget2():startedforblock<0x0007:0x0180414c>objd:0x00017ce0

执行以下查询,会返回4行。

SQL>selectc1fromtest0429wherec1<='04';C1--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------01020304

其对数据块的访问情况如下:

ktrgtc2():startedforblock<0x0007:0x0180414b>objd:0x00017ce0ktrget2():startedforblock<0x0007:0x0180414c>objd:0x00017ce0ktrget2():startedforblock<0x0007:0x0180414c>objd:0x00017ce0ktrget2():startedforblock<0x0007:0x0180414c>objd:0x00017ce0

这里之所以会出现对最左侧叶子块的第三次访问。是因为当其返回第一行后,第二次访问叶子块期间,找到了3行满足条件的记录。由于已达到了ARRAYSIZE的限制,所以,要暂停扫描,返回结果。然后再继续扫描叶子块中的剩余值,看看是否仍有满足条件的记录。因此,会出现对最左侧叶子块的第三次访问。

如果我们发出一条查询最左侧叶子块中的最大值的SQL,又会是什么访问情况呢?

SQL>selectc1fromtest0429wherec1='26';C1--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------26

我们可以看到是访问了全部三个索引块,并且各访问了一次,没有重复访问情况的发生。

ktrgtc2():startedforblock<0x0007:0x0180414b>objd:0x00017ce0ktrget2():startedforblock<0x0007:0x0180414c>objd:0x00017ce0ktrget2():startedforblock<0x0007:0x0180414d>objd:0x00017ce0

之所以发生这种情况,我认为其原因是当其从根块中的指针,访问了最左侧的叶子块,找到一行满足该条件的记录。这时,会如前所述,暂停继续扫描,返回结果。然后继续扫描,但由于在第一次的扫描中,已了解到了该索引条目是本索引块中的最后一个索引条目,所以,就直接沿着最左侧叶子块上指向其后一个叶子块的指针,访问了位于其右侧的叶子块,即访问了“block <0x0007 : 0x0180414d> ”。显然,由于该块中的ROW0已经是&lsquo;27&rsquo;+254个空格了,已经大于了条件值&lsquo;26&rsquo;,因此,结束查询。

如果我们查询的结果是存在于相邻的两个叶子块中时,其访问情况如下:在下面的查询中,有两行记录位于最左侧的叶子块中,而一行记录位于其右侧的叶子块中。

SQL>selectc1fromtest0429wherec1>='25'andc1<='27';C1--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------252627

其中索引块的访问情况如下:

ktrgtc2():startedforblock<0x0007:0x0180414b>objd:0x00017ce0ktrget2():startedforblock<0x0007:0x0180414c>objd:0x00017ce0ktrget2():startedforblock<0x0007:0x0180414c>objd:0x00017ce0ktrget2():startedforblock<0x0007:0x0180414d>objd:0x00017ce0

而当我们查询的结果是存在于相邻的两个叶子块中,并且会凑够ARRAYSIZE参数所指定的3行时,其访问情况会有变化。

SQL>selectc1fromtest0429wherec1>='25'andc1<='28';C1--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------25262728

这时,我们观察到的访问情况如下:

ktrgtc2():startedforblock<0x0007:0x0180414b>objd:0x00017ce0ktrget2():startedforblock<0x0007:0x0180414c>objd:0x00017ce0ktrget2():startedforblock<0x0007:0x0180414c>objd:0x00017ce0ktrget2():startedforblock<0x0007:0x0180414d>objd:0x00017ce0ktrget2():startedforblock<0x0007:0x0180414d>objd:0x00017ce0

如上所示,这里之所以会对位于右侧的叶子块访问2次,其原因是当其访问右侧的叶子块,并获取到满足条件的&lsquo;27&rsquo;和&lsquo;28&rsquo;两条记录时,此时,已经凑够3条了(另1条是&lsquo;26&rsquo;),所以,要暂停扫描,返回结果,然后继续扫描。因此,这时会再次访问右侧的叶子块。

如果换成唯一索引,其访问行为,又会有一些差异。删除原索引,仍在C1列上创建唯一索引。

SQL>dropindexind_test0429_c1;Indexdropped.SQL>createuniqueindexind_unique_test0429_c1ontest0429(c1);Indexcreated.

查看新的唯一索引OBJECT_ID,以便查看其索引树形结构。

SQL>selectobject_id,object_name,object_typefromuser_objectswhereobject_name='IND_UNIQUE_TEST0429_C1';OBJECT_IDOBJECT_NAMEOBJECT_TYPE----------------------------------------------------------------97521IND_UNIQUE_TEST0429_C1INDEXSQL>altersessionsetevents'immediatetracenametreedumplevel97521';Sessionaltered.

如下所示,我们可以看到该结构与此前的树形结构是相同的。

branch:0x180414b25182539(0:nrow:2,level:1)leaf:0x180414c25182540(-1:nrow:26rrow:26)leaf:0x180414d25182541(0:nrow:24rrow:24)-----endtreedump

再次DUMP出根块和最左侧叶子块中的内容,如下所示:

kdxcolev1KDXCOLEVFlags=---kdxcolok0kdxcoopc0x80:opcode=0:iotflags=---isconverted=Ykdxconco1kdxcosdc0kdxconro1kdxcofbo30=0x1ekdxcofeo8049=0x1f71kdxcoavs8019kdxbrlmc25182540=0x180414ckdxbrsno0kdxbrbksz8056kdxbr2urrc3row#0[8049]dba:25182541=0x180414dcol0;len2;(2):3237

我们可以看到根块中,显示位于第二个叶子块中的最小值的起始两位是&lsquo;27&rsquo;,而最左侧叶子块中的内容如下,可以看到该块中的最大值,仍然是&lsquo;26&rsquo;+254个空格。

kdxcolev0KDXCOLEVFlags=---kdxcolok0kdxcoopc0x80:opcode=0:iotflags=---isconverted=Ykdxconco1kdxcosdc0kdxconro26kdxcofbo88=0x58kdxcofeo1116=0x45ckdxcoavs1028kdxlespl0kdxlende0kdxlenxt25182541=0x180414dkdxleprv0=0x0kdxledsz6kdxlebksz8032row#0[7766]flag:------,lock:0,len=266,data:(6):018041470000col0;len256;(256):30312020202020202020202020202020202020202020202020......20202020202020202020202020202020202020202020202020202020202020row#1[7500]flag:------,lock:0,len=266,data:(6):018041470001col0;len256;(256):30322020202020202020202020202020202020202020202020......row#25[1116]flag:------,lock:0,len=266,data:(6):018041470019col0;len256;(256):32362020202020202020202020202020202020202020202020......20202020202020202020202020202020202020202020202020202020202020-----endofleafblockdump-----Enddumpdatablockstsn:7file#:6minblk16715maxblk16716

再次执行只返回1行的查询。

SQL>selectc1fromtest0429wherec1='01';C1--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------01

但对索引的访问方法,已经变为了索引唯一扫描,如下面的执行计划所示:

SQL>select*fromtable(dbms_xplan.display_cursor('','','typical'));PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID3kt1uqh383qbx,childnumber0-------------------------------------selectc1fromtest0429wherec1='01'Planhashvalue:3124258820--------------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|--------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||||1(100)|||*1|INDEXUNIQUESCAN|IND_UNIQUE_TEST0429_C1|1|257|1(0)|00:00:01|--------------------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------1-access("C1"='01')18rowsselected.

这时观察到的对索引块的访问情况如下:

ktrgtc2():startedforblock<0x0007:0x0180414b>objd:0x00017cf1ktrgtc2():startedforblock<0x0007:0x0180414c>objd:0x00017cf1

如上图所示,我们可以看到,并没有发生对最左侧叶子块的两次访问。这是由于唯一索引的特性导致的。由于唯一索引中不会有重复值,所以,当找到一行记录,就不必再判断是否还有其它满足条件的记录了。因为在唯一索引中,要么没有对应条件值,要么就只会有一条。因此,找到一行后,就可以结束了。

如果我们对最左侧叶子块中的最大值做查询,其结果如下:

SQL>selectc1fromtest0429wherec1='26';C1--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------26

如下所示,我们可以看到,仍然是访问2个索引块。并且,不会去访问第二个叶子块。

ktrgtc2():startedforblock<0x0007:0x0180414b>objd:0x00017cf1ktrgtc2():startedforblock<0x0007:0x0180414c>objd:0x00017cf1

但是,当执行以下查询时,情况会发生变化。

SQL>selectc1fromtest0429wherec1<='04';C1--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------01020304

由于WHERE子句中不是等值比较,所以,尽管是在唯一索引上的扫描,但访问方法又回到了索引范围扫描的方法。如下所示:

SQL>select*fromtable(dbms_xplan.display_cursor('','','typical'));PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID9g9p54332fyd4,childnumber0-------------------------------------selectc1fromtest0429wherec1<='04'Planhashvalue:3622766470-------------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|-------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||||2(100)|||*1|INDEXRANGESCAN|IND_UNIQUE_TEST0429_C1|4|1028|2(0)|00:00:01|-------------------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------1-access("C1"<='04')18rowsselected.

而且,其访问索引块的情况,也与此前在非唯一索引上访问,并返回4行结果时的情形相同了。如下所示:

ktrgtc2():startedforblock<0x0007:0x0180414b>objd:0x00017cf1ktrget2():startedforblock<0x0007:0x0180414c>objd:0x00017cf1ktrget2():startedforblock<0x0007:0x0180414c>objd:0x00017cf1ktrget2():startedforblock<0x0007:0x0180414c>objd:0x00017cf1

“如何解决索引扫描时对同一个叶子块访问多次的问题”的内容就介绍到这里了,感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站,小编将为大家输出更多高质量的实用文章!