溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
本篇內容介紹了“如何解決索引掃描時對同一個葉子塊訪問多次的問題”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！
先創建以下測試環境，以重現相關現象。創建測試表，其中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 16716DUMP根塊和最左側葉子塊中的內容到跟蹤文件中。
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進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
