索引扫描时,对同一个叶子块访问多次的原因初探

运维 数据库运维
在观察索引扫描会按何种次序进行索引块的访问时,我发现了一种现象,即会有部分叶子块被访问两次或更多。以下是我自己对这种现象的重现,以及对产生该现象原因的初步判断。但截至目前,我并未找到有官方文档对相关内容的介绍。

[[397702]]

本文转载自微信公众号「数据和云」,作者赵勇。转载本文请联系数据和云公众号。

在观察索引扫描会按何种次序进行索引块的访问时,我发现了一种现象,即会有部分叶子块被访问两次或更多。以下是我自己对这种现象的重现,以及对产生该现象原因的初步判断。但截至目前,我并未找到有官方文档对相关内容的介绍。因此,如果大家有不同的看法,或者可以提供相关的官方文档介绍,也欢迎在文末留言区指正、讨论和提供。

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

  1. SQL> create table test0429 (id number,c1 char(256),v1 varchar2(256)); 
  2.  
  3. Table created. 

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

  1. SQL> insert into test0429 select rownum id,lpad(rownum,2,'0') c1,rownum v1 from dual connect by rownum<=50; 
  2.  
  3. 50 rows created. 
  4.  
  5.  
  6. SQL> commit
  7.  
  8. Commit complete. 

在C1列上创建索引:

  1. SQL> create index ind_test0429_c1 on test0429(c1); 
  2.  
  3. Index created. 

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

  1. SQL> select object_id,object_name,object_type from user_objects where object_name='IND_TEST0429_C1'
  2.  
  3.  OBJECT_ID OBJECT_NAME                 OBJECT_TYPE 
  4. ---------- ----------------------------------- ------------------- 
  5.      97504 IND_TEST0429_C1             INDEX 
  6.  
  7.  
  8. SQL> alter session set events 'immediate trace name treedump level 97504'
  9.  
  10. Session altered. 
  11.  
  12. SQL> select * from v$diag_info; 
  13.  
  14.    INST_ID NAME 
  15. ---------- ---------------------------------------------------------------- 
  16. VALUE 
  17. -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
  18.      1 Diag Enabled 
  19. TRUE 
  20.  
  21.      1 ADR Base 
  22. /oradata/app/oracle 
  23.  
  24.      1 ADR Home 
  25. /oradata/app/oracle/diag/rdbms/orcl/orcl 
  26.  
  27.      1 Diag Trace 
  28. /oradata/app/oracle/diag/rdbms/orcl/orcl/trace 
  29.  
  30.      1 Diag Alert 
  31. /oradata/app/oracle/diag/rdbms/orcl/orcl/alert 
  32.  
  33.      1 Diag Incident 
  34. /oradata/app/oracle/diag/rdbms/orcl/orcl/incident 
  35.  
  36.      1 Diag Cdump 
  37. /oradata/app/oracle/diag/rdbms/orcl/orcl/cdump 
  38.  
  39.      1 Health Monitor 
  40. /oradata/app/oracle/diag/rdbms/orcl/orcl/hm 
  41.  
  42.      1 Default Trace File 
  43. /oradata/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2751.trc 
  44.  
  45.      1 Active Problem Count 
  46.  
  47.      1 Active Incident Count 
  48. 17 
  49.  
  50.  
  51. 11 rows selected. 

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

  1. ----- begin tree dump 
  2. branch: 0x180414b 25182539 (0: nrow: 2, level: 1) 
  3.    leaf: 0x180414c 25182540 (-1: nrow: 26 rrow: 26) 
  4.    leaf: 0x180414d 25182541 (0: nrow: 24 rrow: 24) 
  5. ----- end tree dump 

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

  1. SQL> select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(to_number('&&p3_value','xxxxxxxx')) FILE#, 
  2.        DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(to_number('&&p3_value','xxxxxxxx')) BLOCK# 
  3. from dual;  2    3   
  4. Enter value for p3_value: 180414b 
  5. old   1: select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(to_number('&&p3_value','xxxxxxxx')) FILE#, 
  6. new   1: select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(to_number('180414b','xxxxxxxx')) FILE#, 
  7. old   2:    DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(to_number('&&p3_value','xxxxxxxx')) BLOCK# 
  8. new   2:    DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(to_number('180414b','xxxxxxxx')) BLOCK# 
  9.  
  10.      FILE#     BLOCK# 
  11. ---------- ---------- 
  12.      6  16715 
  13.  
  14. SQL> undefine p3_value 
  15. SQL> select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(to_number('&&p3_value','xxxxxxxx')) FILE#, 
  16.        DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(to_number('&&p3_value','xxxxxxxx')) BLOCK# 
  17. from dual;   2    3   
  18. Enter value for p3_value: 180414c 
  19. old   1: select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(to_number('&&p3_value','xxxxxxxx')) FILE#, 
  20. new   1: select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(to_number('180414c','xxxxxxxx')) FILE#, 
  21. old   2:    DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(to_number('&&p3_value','xxxxxxxx')) BLOCK# 
  22. new   2:    DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(to_number('180414c','xxxxxxxx')) BLOCK# 
  23.  
  24.      FILE#     BLOCK# 
  25. ---------- ---------- 
  26.      6  16716 

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

  1. SQL> alter system dump datafile 6 block min 16715 block max 16716; 
  2.  
  3. System altered. 

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

  1. kdxcolev 1 
  2. KDXCOLEV Flags = - - - 
  3. kdxcolok 0 
  4. kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y 
  5. kdxconco 2 
  6. kdxcosdc 0 
  7. kdxconro 1 
  8. kdxcofbo 30=0x1e 
  9. kdxcofeo 8048=0x1f70 
  10. kdxcoavs 8018 
  11. kdxbrlmc 25182540=0x180414c 
  12. kdxbrsno 0 
  13. kdxbrbksz 8056 
  14. kdxbr2urrc 3 
  15. row#0[8048] dba: 25182541=0x180414d 
  16. col 0; len 2; (2):  32 37 
  17. col 1; TERM 
  18. ----- end of branch block dump ----- 

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

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

  1. kdxcolev 0 
  2. KDXCOLEV Flags = - - - 
  3. kdxcolok 0 
  4. kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y 
  5. kdxconco 2 
  6. kdxcosdc 0 
  7. kdxconro 26 
  8. kdxcofbo 88=0x58 
  9. kdxcofeo 1090=0x442 
  10. kdxcoavs 1002 
  11. kdxlespl 0 
  12. kdxlende 0 
  13. kdxlenxt 25182541=0x180414d 
  14. kdxleprv 0=0x0 
  15. kdxledsz 0 
  16. kdxlebksz 8032 
  17. row#0[7765] flag: ------, lock: 0, len=267 
  18. col 0; len 256; (256): 
  19.  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.  ...... 
  21.  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 
  22.  20 20 20 20 20 20 
  23. col 1; len 6; (6):  01 80 41 47 00 00 
  24. row#1[7498] flag: ------, lock: 0, len=267 
  25. col 0; len 256; (256): 
  26.  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 
  27.  ...... 
  28. row#25[1090] flag: ------, lock: 0, len=267 
  29. col 0; len 256; (256): 
  30.  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 
  31.  ...... 
  32.  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 
  33.  20 20 20 20 20 20 
  34. col 1; len 6; (6):  01 80 41 47 00 19 
  35. ----- end of leaf block dump ----- 
  36. End dump data blocks tsn: 7 file#: 6 minblk 16715 maxblk 16716 

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

  1. SQL> alter session set events '10200 trace name context forever,level 1'
  2.  
  3. Session altered. 

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

  1. SQL> set lines 200 pages 60 
  2.  
  3. SQL> select c1 from test0429 where c1='01';                                                                      
  4.  
  5. C1 
  6. -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
  7. 01 

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

  1. SQL> select * from table(dbms_xplan.display_cursor('','','typical')); 
  2.  
  3. PLAN_TABLE_OUTPUT 
  4. -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
  5. SQL_ID  3kt1uqh283qbx, child number 0 
  6. ------------------------------------- 
  7. select c1 from test0429 where c1='01' 
  8.  
  9. Plan hash value: 1267036809 
  10.  
  11. ------------------------------------------------------------------------------------ 
  12. | Id  | Operation        | Name            | Rows  | Bytes | Cost (%CPU)| Time     | 
  13. ------------------------------------------------------------------------------------ 
  14. |   0 | SELECT STATEMENT |                 |       |       |     1 (100)|          | 
  15. |*  1 |  INDEX RANGE SCAN| IND_TEST0429_C1 |     1 |   257 |     1   (0)| 00:00:01 | 
  16. ------------------------------------------------------------------------------------ 
  17.  
  18.  
  19. Predicate Information (identified by operation id): 
  20. --------------------------------------------------- 
  21.  
  22.    1 - access("C1"='01'
  23.  
  24.  
  25. 18 rows selected. 

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

  1. ktrgtc2(): started for block <0x0007 : 0x0180414b> objd: 0x00017ce0 
  2. ktrget2(): started for block  <0x0007 : 0x0180414c> objd: 0x00017ce0 
  3. ktrget2(): started for block  <0x0007 : 0x0180414c> objd: 0x00017ce0 

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

  1. 访问索引根块,即访问“block <0x0007 : 0x0180414b>”;
  2. 由于条件值‘01’小于根块中,指向第二个叶子块的索引条目中的值‘27’,所以,需要访问索引最左侧的叶子块,即访问“<0x0007 : 0x0180414c>”;
  3. 在最左侧的叶子块中找到了第一行满足条件的记录ROW0。暂停继续扫描,而将第一行返回;
  4. 继续在最左侧的叶子块中查找是否有满足条件的记录。所以,会再次访问最左侧的叶子块;
  5. 在访问ROW1时,得到了值‘02’+254个空格,该值大于‘01’,故整个索引中已不会再有满足条件的记录,所以,结束扫描,退出;
  6. 如果在叶子块的扫描中,还能继续找到满足条件值的记录,就不是每找到一行,就暂停扫描并返回当前结果了,而是根据ARRAYSIZE中的值,每凑够该参数指定的行数,才会暂停扫描并返回结果,然后再继续扫描。当发生“再继续扫描”这个动作时,相应的叶子块会被再一次访问。

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

  1. SQL> show arraysize 
  2. arraysize 15 
  3. SQL> set arraysize 3 
  4. SQL> show arraysize 
  5. arraysize 3 

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

  1. SQL> select c1 from test0429 where c1<='02'
  2.  
  3. C1 
  4. -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
  5. 01 
  6.  
  7.  
  8. 02 

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

  1. ktrgtc2(): started for block <0x0007 : 0x0180414b> objd: 0x00017ce0 
  2. ktrget2(): started for block  <0x0007 : 0x0180414c> objd: 0x00017ce0 
  3. ktrget2(): started for block  <0x0007 : 0x0180414c> objd: 0x00017ce0 

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

  1. SQL> select c1 from test0429 where c1<='03'
  2.  
  3. C1 
  4. -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
  5. 01 
  6.  
  7.  
  8. 02 
  9.  
  10.  
  11. 03 

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

  1. ktrgtc2(): started for block <0x0007 : 0x0180414b> objd: 0x00017ce0 
  2. ktrget2(): started for block  <0x0007 : 0x0180414c> objd: 0x00017ce0 
  3. ktrget2(): started for block  <0x0007 : 0x0180414c> objd: 0x00017ce0 

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

  1. SQL> select c1 from test0429 where c1<='04'
  2.  
  3. C1 
  4. -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
  5. 01 
  6.  
  7.  
  8. 02 
  9.  
  10.  
  11. 03 
  12.  
  13.  
  14. 04 

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

  1. ktrgtc2(): started for block <0x0007 : 0x0180414b> objd: 0x00017ce0 
  2. ktrget2(): started for block  <0x0007 : 0x0180414c> objd: 0x00017ce0 
  3. ktrget2(): started for block  <0x0007 : 0x0180414c> objd: 0x00017ce0 
  4. ktrget2(): started for block  <0x0007 : 0x0180414c> objd: 0x00017ce0 

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

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

  1. SQL> select c1 from test0429 where c1='26'
  2.  
  3. C1 
  4. -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
  5. 26 

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

  1. ktrgtc2(): started for block <0x0007 : 0x0180414b> objd: 0x00017ce0 
  2. ktrget2(): started for block  <0x0007 : 0x0180414c> objd: 0x00017ce0 
  3. ktrget2(): started for block  <0x0007 : 0x0180414d> objd: 0x00017ce0 

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

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

  1. SQL> select c1 from test0429 where c1>='25' and c1<='27'
  2.  
  3. C1 
  4. -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
  5. 25 
  6.  
  7.  
  8. 26 
  9.  
  10.  
  11. 27 

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

  1. ktrgtc2(): started for block <0x0007 : 0x0180414b> objd: 0x00017ce0 
  2. ktrget2(): started for block  <0x0007 : 0x0180414c> objd: 0x00017ce0 
  3. ktrget2(): started for block  <0x0007 : 0x0180414c> objd: 0x00017ce0 
  4. ktrget2(): started for block  <0x0007 : 0x0180414d> objd: 0x00017ce0 

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

  1. SQL> select c1 from test0429 where c1>='25' and c1<='28'
  2.  
  3. C1 
  4. -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
  5. 25 
  6.  
  7.  
  8. 26 
  9.  
  10.  
  11. 27 
  12.  
  13.  
  14. 28 

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

  1. ktrgtc2(): started for block <0x0007 : 0x0180414b> objd: 0x00017ce0 
  2. ktrget2(): started for block  <0x0007 : 0x0180414c> objd: 0x00017ce0 
  3. ktrget2(): started for block  <0x0007 : 0x0180414c> objd: 0x00017ce0 
  4. ktrget2(): started for block  <0x0007 : 0x0180414d> objd: 0x00017ce0 
  5. ktrget2(): started for block  <0x0007 : 0x0180414d> objd: 0x00017ce0 

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

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

  1. SQL> drop index ind_test0429_c1; 
  2.  
  3. Index dropped. 
  4.  
  5. SQL> create unique index ind_unique_test0429_c1 on test0429(c1); 
  6.  
  7. Index created. 

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

  1. SQL> select object_id,object_name,object_type from user_objects where object_name='IND_UNIQUE_TEST0429_C1'
  2.  
  3.  OBJECT_ID OBJECT_NAME                 OBJECT_TYPE 
  4. ---------- ----------------------------------- ------------------- 
  5.      97521 IND_UNIQUE_TEST0429_C1          INDEX 
  6.  
  7. SQL> alter session set events 'immediate trace name treedump level 97521'
  8.  
  9. Session altered. 

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

  1. branch: 0x180414b 25182539 (0: nrow: 2, level: 1) 
  2.    leaf: 0x180414c 25182540 (-1: nrow: 26 rrow: 26) 
  3.    leaf: 0x180414d 25182541 (0: nrow: 24 rrow: 24) 
  4. ----- end tree dump 

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

  1. kdxcolev 1 
  2. KDXCOLEV Flags = - - - 
  3. kdxcolok 0 
  4. kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y 
  5. kdxconco 1 
  6. kdxcosdc 0 
  7. kdxconro 1 
  8. kdxcofbo 30=0x1e 
  9. kdxcofeo 8049=0x1f71 
  10. kdxcoavs 8019 
  11. kdxbrlmc 25182540=0x180414c 
  12. kdxbrsno 0 
  13. kdxbrbksz 8056 
  14. kdxbr2urrc 3 
  15. row#0[8049] dba: 25182541=0x180414d 
  16. col 0; len 2; (2):  32 37 

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

  1. kdxcolev 0 
  2. KDXCOLEV Flags = - - - 
  3. kdxcolok 0 
  4. kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y 
  5. kdxconco 1 
  6. kdxcosdc 0 
  7. kdxconro 26 
  8. kdxcofbo 88=0x58 
  9. kdxcofeo 1116=0x45c 
  10. kdxcoavs 1028 
  11. kdxlespl 0 
  12. kdxlende 0 
  13. kdxlenxt 25182541=0x180414d 
  14. kdxleprv 0=0x0 
  15. kdxledsz 6 
  16. kdxlebksz 8032 
  17. row#0[7766] flag: ------, lock: 0, len=266, data:(6):  01 80 41 47 00 00 
  18. col 0; len 256; (256): 
  19.  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.  ...... 
  21.  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 
  22.  20 20 20 20 20 20 
  23. row#1[7500] flag: ------, lock: 0, len=266, data:(6):  01 80 41 47 00 01 
  24. col 0; len 256; (256): 
  25.  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 
  26.  ...... 
  27. row#25[1116] flag: ------, lock: 0, len=266, data:(6):  01 80 41 47 00 19 
  28. col 0; len 256; (256): 
  29.  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 
  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 
  32.  20 20 20 20 20 20 
  33. ----- end of leaf block dump ----- 
  34. End dump data blocks tsn: 7 file#: 6 minblk 16715 maxblk 16716 

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

  1. SQL> select c1 from test0429 where c1='01'
  2.  
  3. C1 
  4. -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
  5. 01 

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

  1. SQL> select * from table(dbms_xplan.display_cursor('','','typical')); 
  2.  
  3. PLAN_TABLE_OUTPUT 
  4. -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
  5. SQL_ID  3kt1uqh283qbx, child number 0 
  6. ------------------------------------- 
  7. select c1 from test0429 where c1='01' 
  8.  
  9. Plan hash value: 3124258820 
  10.  
  11. -------------------------------------------------------------------------------------------- 
  12. | Id  | Operation         | Name                   | Rows  | Bytes | Cost (%CPU)| Time     | 
  13. -------------------------------------------------------------------------------------------- 
  14. |   0 | SELECT STATEMENT  |                        |       |       |     1 (100)|          | 
  15. |*  1 |  INDEX UNIQUE SCAN| IND_UNIQUE_TEST0429_C1 |     1 |   257 |     1   (0)| 00:00:01 | 
  16. -------------------------------------------------------------------------------------------- 
  17.  
  18. Predicate Information (identified by operation id): 
  19. --------------------------------------------------- 
  20.  
  21.    1 - access("C1"='01'
  22.  
  23.  
  24. 18 rows selected. 

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

  1. ktrgtc2(): started for block <0x0007 : 0x0180414b> objd: 0x00017cf1  
  2. ktrgtc2(): started for block <0x0007 : 0x0180414c> objd: 0x00017cf1 

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

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

  1. SQL> select c1 from test0429 where c1='26'
  2.  
  3. C1 
  4. -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
  5. 26 

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

  1. ktrgtc2(): started for block <0x0007 : 0x0180414b> objd: 0x00017cf1 
  2. ktrgtc2(): started for block <0x0007 : 0x0180414c> objd: 0x00017cf1 

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

  1. SQL> select c1 from test0429 where c1<='04'
  2.  
  3. C1 
  4. -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
  5. 01 
  6.  
  7.  
  8. 02 
  9.  
  10.  
  11. 03 
  12.  
  13.  
  14. 04 

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

  1. SQL> select * from table(dbms_xplan.display_cursor('','','typical')); 
  2.  
  3. PLAN_TABLE_OUTPUT 
  4. -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
  5. SQL_ID  9g9p54332fyd4, child number 0 
  6. ------------------------------------- 
  7. select c1 from test0429 where c1<='04' 
  8.  
  9. Plan hash value: 3622766470 
  10.  
  11. ------------------------------------------------------------------------------------------- 
  12. | Id  | Operation        | Name                   | Rows  | Bytes | Cost (%CPU)| Time     | 
  13. ------------------------------------------------------------------------------------------- 
  14. |   0 | SELECT STATEMENT |                        |       |       |     2 (100)|          | 
  15. |*  1 |  INDEX RANGE SCAN| IND_UNIQUE_TEST0429_C1 |     4 |  1028 |     2   (0)| 00:00:01 | 
  16. ------------------------------------------------------------------------------------------- 
  17.  
  18. Predicate Information (identified by operation id): 
  19. --------------------------------------------------- 
  20.  
  21.    1 - access("C1"<='04'
  22.  
  23.  
  24. 18 rows selected. 

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

  1. ktrgtc2(): started for block <0x0007 : 0x0180414b> objd: 0x00017cf1 
  2. ktrget2(): started for block  <0x0007 : 0x0180414c> objd: 0x00017cf1 
  3. ktrget2(): started for block  <0x0007 : 0x0180414c> objd: 0x00017cf1 
  4. ktrget2(): started for block  <0x0007 : 0x0180414c> objd: 0x00017cf1 

关于作者

赵勇,云和恩墨北区SQL审核和优化团队总监,从业超过20年,专职于SQL优化与SQL质量管控的服务工作,作为项目负责人和主要实施人员,深度融入金融、保险、政府,运营商等多个行业,结合行业系统特性,为客户优化了大量问题SQL,同时也为运营商、银行等客户的核心系统提供SQL质量审核服务,助其防患于未然,为系统高质量运行提供保障。

 

责任编辑:武晓燕 来源: 数据和云
相关推荐

2016-12-15 08:54:52

线程sessionopenSession

2015-10-16 13:41:52

程序对象设计

2016-12-20 13:55:52

2009-06-09 12:38:12

NetBeanseclipse

2019-08-20 10:24:39

HTTPSSSHLinux

2009-11-20 17:10:43

Oracle B树索引

2022-08-11 16:01:26

勒索软件网络攻击

2021-08-16 20:48:34

嵌入式单片机信息

2024-09-05 16:01:55

2024-04-28 18:31:03

2022-07-26 00:00:02

TCPUDPMAC

2017-08-17 10:53:10

Google代码仓库

2024-03-18 08:21:06

TCPUDP协议

2015-11-12 15:14:48

ZD至顶网CIO与应用

2021-04-08 14:51:20

Python编码语言

2024-03-05 10:07:22

TCPUDP协议

2023-09-13 13:05:01

Java项目

2019-01-28 09:43:21

IP地址子网掩码

2019-07-09 14:42:17

SQLexplain索引

2021-06-15 20:56:39

Python正则表达式
点赞
收藏

51CTO技术栈公众号