本篇内容介绍了“如何解决索引扫描时对同一个叶子块访问多次的问题”的有关知识,在实际案例的操作过程中,不少人都会遇到这样的困境,接下来就让小编带领大家学习一下如何处理这些情况吧!希望大家仔细阅读,能够学有所成!
先创建以下测试环境,以重现相关现象。创建测试表,其中C1列为CHAR(256),目的是使该列占用字节数较多,使得后面在该列上创建索引时,可以用较少的行数构建出2层的索引。
SQL> create table test0429 (id number,c1 char(256),v1 varchar2(256)); Table created.
C1中插入的值为‘01’+254个空格,‘02’+254个空格…这样的值。
SQL> insert into test0429 select rownum id,lpad(rownum,2,'0') c1,rownum v1 from dual connect by rownum<=50; 50 rows created. SQL> commit; Commit complete.
在C1列上创建索引:
SQL> create index ind_test0429_c1 on test0429(c1); Index created.
查询该索引的OBJECT_ID,以便查看其树形结构。
SQL> select object_id,object_name,object_type from user_objects where object_name='IND_TEST0429_C1'; OBJECT_ID OBJECT_NAME OBJECT_TYPE ---------- ----------------------------------- ------------------- 97504 IND_TEST0429_C1 INDEX SQL> alter session set events 'immediate trace name treedump level 97504'; Session altered. SQL> select * from v$diag_info; INST_ID NAME ---------- ---------------------------------------------------------------- VALUE -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 Diag Enabled TRUE 1 ADR Base /oradata/app/oracle 1 ADR Home /oradata/app/oracle/diag/rdbms/orcl/orcl 1 Diag Trace /oradata/app/oracle/diag/rdbms/orcl/orcl/trace 1 Diag Alert /oradata/app/oracle/diag/rdbms/orcl/orcl/alert 1 Diag Incident /oradata/app/oracle/diag/rdbms/orcl/orcl/incident 1 Diag Cdump /oradata/app/oracle/diag/rdbms/orcl/orcl/cdump 1 Health Monitor /oradata/app/oracle/diag/rdbms/orcl/orcl/hm 1 Default Trace File /oradata/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2751.trc 1 Active Problem Count 5 1 Active Incident Count 17 11 rows selected.
在对应的跟踪文件中,看到的索引结构为1个根节点,2个叶子节点。如下所示:
----- begin tree dump branch: 0x180414b 25182539 (0: nrow: 2, level: 1) leaf: 0x180414c 25182540 (-1: nrow: 26 rrow: 26) leaf: 0x180414d 25182541 (0: nrow: 24 rrow: 24) ----- end tree dump
查询根节点和最左侧叶子节点的数据块所在文件块及块号,准备DUMP其数据块,以便查看其中的内容。
SQL> select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(to_number('&&p3_value','xxxxxxxx')) FILE#, DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(to_number('&&p3_value','xxxxxxxx')) BLOCK# from dual; 2 3 Enter value for p3_value: 180414b old 1: select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(to_number('&&p3_value','xxxxxxxx')) FILE#, new 1: select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(to_number('180414b','xxxxxxxx')) FILE#, old 2: DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(to_number('&&p3_value','xxxxxxxx')) BLOCK# new 2: DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(to_number('180414b','xxxxxxxx')) BLOCK# FILE# BLOCK# ---------- ---------- 6 16715 SQL> undefine p3_value SQL> select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(to_number('&&p3_value','xxxxxxxx')) FILE#, DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(to_number('&&p3_value','xxxxxxxx')) BLOCK# from dual; 2 3 Enter value for p3_value: 180414c old 1: select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(to_number('&&p3_value','xxxxxxxx')) FILE#, new 1: select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(to_number('180414c','xxxxxxxx')) FILE#, old 2: DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(to_number('&&p3_value','xxxxxxxx')) BLOCK# new 2: DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(to_number('180414c','xxxxxxxx')) BLOCK# FILE# BLOCK# ---------- ---------- 6 16716
DUMP根块和最左侧叶子块中的内容到跟踪文件中。
SQL> alter system dump datafile 6 block min 16715 block max 16716; System altered.
从跟踪文件中,可以看到根块中的主要内容如下所示(为节省篇幅,以下只列出与本主题相关的主要内容,以下其它类似内容亦做了相关处理,不再重复说明):
kdxcolev 1 KDXCOLEV Flags = - - - kdxcolok 0 kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y kdxconco 2 kdxcosdc 0 kdxconro 1 kdxcofbo 30=0x1e kdxcofeo 8048=0x1f70 kdxcoavs 8018 kdxbrlmc 25182540=0x180414c kdxbrsno 0 kdxbrbksz 8056 kdxbr2urrc 3 row#0[8048] dba: 25182541=0x180414d col 0; len 2; (2): 32 37 col 1; TERM ----- end of branch block dump -----
从上面的倒数第三行的内容中可知,最右侧的叶子块中的最小索引键值为‘27’+254个空格。
从跟踪文件中,可以看到最左侧叶子块中的主要内容如下所示:
kdxcolev 0 KDXCOLEV Flags = - - - kdxcolok 0 kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y kdxconco 2 kdxcosdc 0 kdxconro 26 kdxcofbo 88=0x58 kdxcofeo 1090=0x442 kdxcoavs 1002 kdxlespl 0 kdxlende 0 kdxlenxt 25182541=0x180414d kdxleprv 0=0x0 kdxledsz 0 kdxlebksz 8032 row#0[7765] flag: ------, lock: 0, len=267 col 0; len 256; (256): 30 31 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 ...... 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 col 1; len 6; (6): 01 80 41 47 00 00 row#1[7498] flag: ------, lock: 0, len=267 col 0; len 256; (256): 30 32 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 ...... row#25[1090] flag: ------, lock: 0, len=267 col 0; len 256; (256): 32 36 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 ...... 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 col 1; len 6; (6): 01 80 41 47 00 19 ----- end of leaf block dump ----- End dump data blocks tsn: 7 file#: 6 minblk 16715 maxblk 16716
为跟踪索引数据块被访问的情况,打开10200跟踪事件。
SQL> alter session set events '10200 trace name context forever,level 1'; Session altered.
查询位于最左侧叶子块中的数据,由于是等值查询,且C1列上无重复值,故以下查询会返回1行。
SQL> set lines 200 pages 60 SQL> select c1 from test0429 where c1='01'; C1 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 01
由于我们在C1列上创建的索引不是唯一索引,所以此时,对索引的访问方法为索引范围扫描。如下图所示:
SQL> select * from table(dbms_xplan.display_cursor('','','typical')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 3kt1uqh383qbx, child number 0 ------------------------------------- select c1 from test0429 where c1='01' Plan hash value: 1267036809 ------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | 1 (100)| | |* 1 | INDEX RANGE SCAN| IND_TEST0429_C1 | 1 | 257 | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("C1"='01') 18 rows selected.
查看10200跟踪文件中的输出,我们可以看到先访问了索引根块,然后访问了最左侧的叶子块。这是符合预期的。但我们可以看到,最左侧的叶子块访问了2次。
ktrgtc2(): started for block <0x0007 : 0x0180414b> objd: 0x00017ce0 ktrget2(): started for block <0x0007 : 0x0180414c> objd: 0x00017ce0 ktrget2(): started for block <0x0007 : 0x0180414c> objd: 0x00017ce0
之所以被访问两次,我认为其过程如下:
鸿蒙官方战略合作共建——HarmonyOS技术社区
访问索引根块,即访问“block <0x0007 : 0x0180414b>”;
由于条件值‘01’小于根块中,指向第二个叶子块的索引条目中的值‘27’,所以,需要访问索引最左侧的叶子块,即访问“<0x0007 : 0x0180414c>”;
在最左侧的叶子块中找到了第一行满足条件的记录ROW0。暂停继续扫描,而将第一行返回;
继续在最左侧的叶子块中查找是否有满足条件的记录。所以,会再次访问最左侧的叶子块;
在访问ROW1时,得到了值‘02’+254个空格,该值大于‘01’,故整个索引中已不会再有满足条件的记录,所以,结束扫描,退出;
如果在叶子块的扫描中,还能继续找到满足条件值的记录,就不是每找到一行,就暂停扫描并返回当前结果了,而是根据ARRAYSIZE中的值,每凑够该参数指定的行数,才会暂停扫描并返回结果,然后再继续扫描。当发生“再继续扫描”这个动作时,相应的叶子块会被再一次访问。
针对6中所述,我们进行如下测试。将ARRAYSIZE设置为3,即每凑够3行即暂停扫描,返回结果。而该参数的默认值为15。
SQL> show arraysize arraysize 15 SQL> set arraysize 3 SQL> show arraysize arraysize 3
执行以下查询,应该返回2行。
SQL> select c1 from test0429 where c1<='02'; C1 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 01 02
其对数据块的访问情况如下:
ktrgtc2(): started for block <0x0007 : 0x0180414b> objd: 0x00017ce0 ktrget2(): started for block <0x0007 : 0x0180414c> objd: 0x00017ce0 ktrget2(): started for block <0x0007 : 0x0180414c> objd: 0x00017ce0
执行以下查询,会返回3行。
SQL> select c1 from test0429 where c1<='03'; C1 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 01 02 03
其对数据块的访问情况如下:
ktrgtc2(): started for block <0x0007 : 0x0180414b> objd: 0x00017ce0 ktrget2(): started for block <0x0007 : 0x0180414c> objd: 0x00017ce0 ktrget2(): started for block <0x0007 : 0x0180414c> objd: 0x00017ce0
执行以下查询,会返回4行。
SQL> select c1 from test0429 where c1<='04'; C1 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 01 02 03 04
其对数据块的访问情况如下:
ktrgtc2(): started for block <0x0007 : 0x0180414b> objd: 0x00017ce0 ktrget2(): started for block <0x0007 : 0x0180414c> objd: 0x00017ce0 ktrget2(): started for block <0x0007 : 0x0180414c> objd: 0x00017ce0 ktrget2(): started for block <0x0007 : 0x0180414c> objd: 0x00017ce0
这里之所以会出现对最左侧叶子块的第三次访问。是因为当其返回第一行后,第二次访问叶子块期间,找到了3行满足条件的记录。由于已达到了ARRAYSIZE的限制,所以,要暂停扫描,返回结果。然后再继续扫描叶子块中的剩余值,看看是否仍有满足条件的记录。因此,会出现对最左侧叶子块的第三次访问。
如果我们发出一条查询最左侧叶子块中的最大值的SQL,又会是什么访问情况呢?
SQL> select c1 from test0429 where c1='26'; C1 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 26
我们可以看到是访问了全部三个索引块,并且各访问了一次,没有重复访问情况的发生。
ktrgtc2(): started for block <0x0007 : 0x0180414b> objd: 0x00017ce0 ktrget2(): started for block <0x0007 : 0x0180414c> objd: 0x00017ce0 ktrget2(): started for block <0x0007 : 0x0180414d> objd: 0x00017ce0
之所以发生这种情况,我认为其原因是当其从根块中的指针,访问了最左侧的叶子块,找到一行满足该条件的记录。这时,会如前所述,暂停继续扫描,返回结果。然后继续扫描,但由于在第一次的扫描中,已了解到了该索引条目是本索引块中的最后一个索引条目,所以,就直接沿着最左侧叶子块上指向其后一个叶子块的指针,访问了位于其右侧的叶子块,即访问了“block <0x0007 : 0x0180414d> ”。显然,由于该块中的ROW0已经是‘27’+254个空格了,已经大于了条件值‘26’,因此,结束查询。
如果我们查询的结果是存在于相邻的两个叶子块中时,其访问情况如下:在下面的查询中,有两行记录位于最左侧的叶子块中,而一行记录位于其右侧的叶子块中。
SQL> select c1 from test0429 where c1>='25' and c1<='27'; C1 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 25 26 27
其中索引块的访问情况如下:
ktrgtc2(): started for block <0x0007 : 0x0180414b> objd: 0x00017ce0 ktrget2(): started for block <0x0007 : 0x0180414c> objd: 0x00017ce0 ktrget2(): started for block <0x0007 : 0x0180414c> objd: 0x00017ce0 ktrget2(): started for block <0x0007 : 0x0180414d> objd: 0x00017ce0
而当我们查询的结果是存在于相邻的两个叶子块中,并且会凑够ARRAYSIZE参数所指定的3行时,其访问情况会有变化。
SQL> select c1 from test0429 where c1>='25' and c1<='28'; C1 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 25 26 27 28
这时,我们观察到的访问情况如下:
ktrgtc2(): started for block <0x0007 : 0x0180414b> objd: 0x00017ce0 ktrget2(): started for block <0x0007 : 0x0180414c> objd: 0x00017ce0 ktrget2(): started for block <0x0007 : 0x0180414c> objd: 0x00017ce0 ktrget2(): started for block <0x0007 : 0x0180414d> objd: 0x00017ce0 ktrget2(): started for block <0x0007 : 0x0180414d> objd: 0x00017ce0
如上所示,这里之所以会对位于右侧的叶子块访问2次,其原因是当其访问右侧的叶子块,并获取到满足条件的‘27’和‘28’两条记录时,此时,已经凑够3条了(另1条是‘26’),所以,要暂停扫描,返回结果,然后继续扫描。因此,这时会再次访问右侧的叶子块。
如果换成唯一索引,其访问行为,又会有一些差异。删除原索引,仍在C1列上创建唯一索引。
SQL> drop index ind_test0429_c1; Index dropped. SQL> create unique index ind_unique_test0429_c1 on test0429(c1); Index created.
查看新的唯一索引OBJECT_ID,以便查看其索引树形结构。
SQL> select object_id,object_name,object_type from user_objects where object_name='IND_UNIQUE_TEST0429_C1'; OBJECT_ID OBJECT_NAME OBJECT_TYPE ---------- ----------------------------------- ------------------- 97521 IND_UNIQUE_TEST0429_C1 INDEX SQL> alter session set events 'immediate trace name treedump level 97521'; Session altered.
如下所示,我们可以看到该结构与此前的树形结构是相同的。
branch: 0x180414b 25182539 (0: nrow: 2, level: 1) leaf: 0x180414c 25182540 (-1: nrow: 26 rrow: 26) leaf: 0x180414d 25182541 (0: nrow: 24 rrow: 24) ----- end tree dump
再次DUMP出根块和最左侧叶子块中的内容,如下所示:
kdxcolev 1 KDXCOLEV Flags = - - - kdxcolok 0 kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y kdxconco 1 kdxcosdc 0 kdxconro 1 kdxcofbo 30=0x1e kdxcofeo 8049=0x1f71 kdxcoavs 8019 kdxbrlmc 25182540=0x180414c kdxbrsno 0 kdxbrbksz 8056 kdxbr2urrc 3 row#0[8049] dba: 25182541=0x180414d col 0; len 2; (2): 32 37
我们可以看到根块中,显示位于第二个叶子块中的最小值的起始两位是‘27’,而最左侧叶子块中的内容如下,可以看到该块中的最大值,仍然是‘26’+254个空格。
kdxcolev 0 KDXCOLEV Flags = - - - kdxcolok 0 kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y kdxconco 1 kdxcosdc 0 kdxconro 26 kdxcofbo 88=0x58 kdxcofeo 1116=0x45c kdxcoavs 1028 kdxlespl 0 kdxlende 0 kdxlenxt 25182541=0x180414d kdxleprv 0=0x0 kdxledsz 6 kdxlebksz 8032 row#0[7766] flag: ------, lock: 0, len=266, data:(6): 01 80 41 47 00 00 col 0; len 256; (256): 30 31 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 ...... 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 row#1[7500] flag: ------, lock: 0, len=266, data:(6): 01 80 41 47 00 01 col 0; len 256; (256): 30 32 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 ...... row#25[1116] flag: ------, lock: 0, len=266, data:(6): 01 80 41 47 00 19 col 0; len 256; (256): 32 36 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 ...... 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 ----- end of leaf block dump ----- End dump data blocks tsn: 7 file#: 6 minblk 16715 maxblk 16716
再次执行只返回1行的查询。
SQL> select c1 from test0429 where c1='01'; C1 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 01
但对索引的访问方法,已经变为了索引唯一扫描,如下面的执行计划所示:
SQL> select * from table(dbms_xplan.display_cursor('','','typical')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 3kt1uqh383qbx, child number 0 ------------------------------------- select c1 from test0429 where c1='01' Plan hash value: 3124258820 -------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 1 (100)| | |* 1 | INDEX UNIQUE SCAN| IND_UNIQUE_TEST0429_C1 | 1 | 257 | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("C1"='01') 18 rows selected.
这时观察到的对索引块的访问情况如下:
ktrgtc2(): started for block <0x0007 : 0x0180414b> objd: 0x00017cf1 ktrgtc2(): started for block <0x0007 : 0x0180414c> objd: 0x00017cf1
如上图所示,我们可以看到,并没有发生对最左侧叶子块的两次访问。这是由于唯一索引的特性导致的。由于唯一索引中不会有重复值,所以,当找到一行记录,就不必再判断是否还有其它满足条件的记录了。因为在唯一索引中,要么没有对应条件值,要么就只会有一条。因此,找到一行后,就可以结束了。
如果我们对最左侧叶子块中的最大值做查询,其结果如下:
SQL> select c1 from test0429 where c1='26'; C1 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 26
如下所示,我们可以看到,仍然是访问2个索引块。并且,不会去访问第二个叶子块。
ktrgtc2(): started for block <0x0007 : 0x0180414b> objd: 0x00017cf1 ktrgtc2(): started for block <0x0007 : 0x0180414c> objd: 0x00017cf1
但是,当执行以下查询时,情况会发生变化。
SQL> select c1 from test0429 where c1<='04'; C1 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 01 02 03 04
由于WHERE子句中不是等值比较,所以,尽管是在唯一索引上的扫描,但访问方法又回到了索引范围扫描的方法。如下所示:
SQL> select * from table(dbms_xplan.display_cursor('','','typical')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 9g9p54332fyd4, child number 0 ------------------------------------- select c1 from test0429 where c1<='04' Plan hash value: 3622766470 ------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 (100)| | |* 1 | INDEX RANGE SCAN| IND_UNIQUE_TEST0429_C1 | 4 | 1028 | 2 (0)| 00:00:01 | ------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("C1"<='04') 18 rows selected.
而且,其访问索引块的情况,也与此前在非唯一索引上访问,并返回4行结果时的情形相同了。如下所示:
ktrgtc2(): started for block <0x0007 : 0x0180414b> objd: 0x00017cf1 ktrget2(): started for block <0x0007 : 0x0180414c> objd: 0x00017cf1 ktrget2(): started for block <0x0007 : 0x0180414c> objd: 0x00017cf1 ktrget2(): started for block <0x0007 : 0x0180414c> objd: 0x00017cf1
“如何解决索引扫描时对同一个叶子块访问多次的问题”的内容就介绍到这里了,感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站,小编将为大家输出更多高质量的实用文章!
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。