oracle 索引块结构及索引访问路径

最近研究了下oracle的B-tree索引，有了些发现。放在这里，与大家共享。

这篇文章主要介绍oracle B-tree索引的internal结构，顺便说说几种索引扫描方式对于索引块的扫描路径。

首先放上官方文档，对索引结构的图形描述。图片有点大，我不知道如何在显示的时候缩小，将就看吧。

上面是一个deep为3的索引结构图。deep的值就是从索引的root block到索引的leaf block所要经历的block数。

说道索引的internal结构，我们还是先dump出索引的块来看看。

首先，要找到索引的块在哪里。举个例子，使用如下的语句：

SQL> select a.owner,a.index_name,a.blevel from sys.dba_indexes a where a.index_name='IDX_TEST_OBJECTS2_INDEX1';


OWNER        INDEX_NAME  BLEVEL
------------------------------ ------------------------------ ----------
SCOTT        IDX_TEST_OBJECTS2_INDEX1        1

select * from dba_extents a where a.owner='SCOTT' and a.segment_name='IDX_TEST_OBJECTS2_INDEX1' order by a.EXTENT_ID;

我这里的例子查出来如下结果：我是截取的，举例子一条就够了。注意下，我这里拿出了extent_id为0的记录。这是这个索引段的第一个extent。包含了大量的信息。asm的管理结构包含在其中。

OWNER SCOTT
SEGMENT_NAME IDX_TEST_OBJECTS2_INDEX1
PARTITION_NAME
SEGMENT_TYPE INDEX
TABLESPACE_NAME USERS
EXTENT_ID 0
FILE_ID 4
BLOCK_ID 14400
BYTES 65536
BLOCKS 8
RELATIVE_FNO 4

然后根据结果，来进行dump。

SQL> oradebug setmypid;
Statement processed.
SQL> oradebug tracefile_name;
/home/oracle/app/oracle/diag/rdbms/uorcl/iorcl/trace/iorcl_ora_70023.trc
SQL> alter system dump datafile &file block min &min block max &max;
Enter value for file: 4
Enter value for min: 14400
Enter value for max: 14407
old   1: alter system dump datafile &file block min &min block max &max
new   1: alter system dump datafile 4 block min 14400 block max 14407
System altered.
SQL> 

拿到dump文件后，可以打开看看。

这里要插一下，其实可以先用treedump将整个索引结构框架dump出来，这样可以有个整体印象。如下：

SQL> select owner,object_name,object_id from sys.dba_objects a where a.object_name='IDX_TEST_OBJECTS2_INDEX1';


OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
 OBJECT_ID
----------
SCOTT
IDX_TEST_OBJECTS2_INDEX1
     90549

SQL> oradebug setmypid;
Statement processed.
SQL> oradebug tracefile_name;
/home/oracle/app/oracle/diag/rdbms/uorcl/iorcl/trace/iorcl_ora_70702.trc

SQL> alter session set events 'immediate trace name treedump level 90549';


Session altered.

截取部分结果如下。这里可以看到，我这里只有一个branch块，剩下的都是leaf block。为什么呢，因为我这个索引的数据比较少，他的blevel是1.高度仅仅是2而已。

 38 branch: 0x1003843 16791619 (0: nrow: 263, level: 1)
 39    leaf: 0x1003844 16791620 (-1: nrow: 256 rrow: 256)
 40    leaf: 0x1003845 16791621 (0: nrow: 256 rrow: 256)
 41    leaf: 0x1003846 16791622 (1: nrow: 256 rrow: 256)
 42    leaf: 0x1003847 16791623 (2: nrow: 256 rrow: 256)
 43    leaf: 0x1003848 16791624 (3: nrow: 256 rrow: 256)

然后再回来看看刚才的dump结构。多的不说，主要说下关键的几个地方。

同样是截取了部分。

[oracle@localhost ~]$ vi /home/oracle/app/oracle/diag/rdbms/uorcl/iorcl/trace/iorcl_ora_69063.trc


  627 Block header dump:  0x01003843   --0x01003843是当前数据块地址
 628  Object id on Block? Y
 629  seg/obj: 0x161b5  csc: 0x00.3c768c  itc: 1  flg: E  typ: 2 - INDEX
 630      brn: 0  bdba: 0x1003840 ver: 0x01 opc: 0
 631      inc: 0  exflg: 0
 632 
 633  Itl           Xid                  Uba         Flag  Lck        Scn/Fsc       --这是初始化事务槽部分
 634 0x01   0xffff.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.003c768c
 635 Branch block dump
 636 =================
 637 header address 139967411788364=0x7f4cb3dc1a4c
 638 kdxcolev 1      --这个地方，index  block的level。这里是1，因为这个索引深度为2，所以这个block就是root了。
 639 KDXCOLEV Flags = - - -
 640 kdxcolok 0
 641 kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
 642 kdxconco 3
 643 kdxcosdc 0
 644 kdxconro 262
 645 kdxcofbo 552=0x228
 646 kdxcofeo 3916=0xf4c
 647 kdxcoavs 3364
 648 kdxbrlmc 16791620=0x1003844      --这个地方要注意一下，16791620=0x1003844一个是10进制，一个是16进制。这是一个数据块的地址。指向的leaf/branch block所包含的index  key的最大值小于或者等于这个root块所连接的其它所有的leaf/branch block。实际上就是这个root块其下层block的首块。
 649 kdxbrsno 0
 650 kdxbrbksz 8056
 651 kdxbr2urrc 0
 652 row#0[8034] dba: 16791621=0x1003845    --这个是包含下面这个col 0的index  key的block地址。这里有个点，标位”注1”，放在最后说。
 653 col 0; len 11; (11):  41 50 45 58 5f 30 33 30 32 30 30    --这是复合索引首列的index  key。这里标位”注2”，后面说
 654 col 1; len 4; (4):  c3 09 2d 63  -这是复合索引第二列的index  key  这里标注“注3”
 655 col 2; TERM
 656 row#1[8012] dba: 16791622=0x1003846
 657 col 0; len 11; (11):  41 50 45 58 5f 30 33 30 32 30 30
 658 col 1; len 4; (4):  c3 09 30 37
 659 col 2; TERM  --这里没有显示出来值，有些branch  block会显示部分rowid的信息

上面说了root  或者branch  block。其实leaf  block有两个不同的地方需要介绍下。

5556  Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
5557 0x01   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
5558 0x02   0xffff.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.003c768c
5559 Leaf block dump
5560 ===============
5561 header address 139968438844004=0x7f4cf113ba64
5562 kdxcolev 0
5563 KDXCOLEV Flags = - - -
5564 kdxcolok 0
5565 kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
5566 kdxconco 3
5567 kdxcosdc 0
5568 kdxconro 256
5569 kdxcofbo 548=0x224
5570 kdxcofeo 1378=0x562
5571 kdxcoavs 830
5572 kdxlespl 0
5573 kdxlende 0
5574 kdxlenxt 16791624=0x1003848    --这里就是leaf block双向链接的所在了。
5575 kdxleprv 16791622=0x1003846    --同上。
5576 kdxledsz 0
5577 kdxlebksz 8032
5578 row#0[8006] flag: ------, lock: 0, len=26
5579 col 0; len 11; (11):  41 50 45 58 5f 30 33 30 32 30 30
5580 col 1; len 4; (4):  c3 09 33 0b
5581 col 2; len 6; (6):  01 00 3c cc 00 1f    --这里是index  key对应的表行的rowid。

现在来说下几种索引访问方式：

这里先放一张注解的图。这个是打了标号的图。说起来方便。

1  index  full  scan ：有序的索引全扫描。扫描出来的结果是排序的。按照index  key，rowid进行排序。访问索引的路径大致为1>2>5>6>7>8;

2  index fast full scan:索引快速全扫描。扫描出来的结构是无序的。这个访问路径与上面的图无关。与table full  scan类似，是从段头的bitmap 开始的。

3  index range  scan：索引范围扫描，访问路径大致为1>2>5>6，与1类似。但是是由范围的。也可以是1>2>6或者是1>4>7>8.本质在于入口必须从root>branch>leaf，访问第一个leaf后，后面就依赖leaf间的双向链接获取下一个leaf block。

4  index  skip  scan：索引跳跃扫描，访问路径可以有多个入口。1>2>5和1>4>7.从5到7之间“skip”了6.

上面的结论也是我推断出来的，暂时没有时间验证。但是验证方法我可以给出来：

     使用10046跟踪一次索引访问过程。但是要在重启库后，在访问相关对象前进行。因为这时候，对于所有block的访问都会留下记录。

最后来解释下上面遗留的两个问题：

注1： 652 row#0[8034] dba: 16791621=0x1003845 这个可以通过如下sql获取对应块的fileid和block号：

          select sys.dbms_utility.data_block_address_file(rn)||','||sys.dbms_utility.data_block_address_block(rn) 
from (select 16791621 rn from dual);

注2： 653 col 0; len 11; (11):  41 50 45 58 5f 30 33 30 32 30 30   这个值可以用下面的方法取出其中的index key的值。但是注意，这个方法值针对字符类型有效。

create or replace procedure sys.hex2char(i_vc_input in varchar2) is
o_vc_return_flag varchar2(4000);
i_vc_input_compress varchar2(4060);
vc_characterset varchar2(4000);
type type_character_number is table of number index by binary_integer;
characters_number type_character_number;
j number;
n_temp number;
n_skipflag number;
begin
  if( instr(i_vc_input,',') > 0 ) then
      i_vc_input_compress := trim(replace(i_vc_input,',',''));
  elsif ( instr(i_vc_input, ' ') > 0 ) then
        i_vc_input_compress := trim(replace(i_vc_input,' ',''));
  else
        i_vc_input_compress := trim(i_vc_input);
  end if;
  select value into vc_characterset from nls_database_parameters where parameter='NLS_CHARACTERSET';
  j:= 1;
  n_skipflag:=0;
  for i in 1 .. length(i_vc_input_compress) loop
    if  n_skipflag > 0  then 
      n_skipflag := n_skipflag - 1; 
    end if;
    if ( n_skipflag = 0 ) then
          select to_number(substr(i_vc_input_compress,i,2),'XXXXXXXXXXXX') into n_temp from dual;
       if (n_temp < 128 ) then
         characters_number(j) := n_temp;
         j := j + 1; n_skipflag := 2;
       elsif ( vc_characterset = 'ZHS16GBK' )then
         select to_number(substr(i_vc_input_compress,i,4),'XXXXXXXXXXXX') into n_temp from dual;
         characters_number(j) := n_temp;
         j := j + 1;
         n_skipflag := 4;
       elsif( vc_characterset='AL32UTF8' ) then
            select to_number(substr(i_vc_input_compress,i,6),'XXXXXXXXXXXX') into n_temp from dual;
             characters_number(j) := n_temp; j := j + 1; n_skipflag := 6;
       else select to_number(substr(i_vc_input_compress,i,4),'XXXXXXXXXXXX') into n_temp from dual;
            characters_number(j) := n_temp;
            j := j + 1; n_skipflag := 4;
       end if;
    end if;
  end loop;
  if ( characters_number.count > 0 ) then
    for k in characters_number.first .. characters_number.last loop
         if( characters_number(k) > 31 ) then
             dbms_output.put(chr(characters_number(k)));
         end if;
    end loop;
  end if;
dbms_output.put_line(chr(10));
exception
when others then
     o_vc_return_flag :=  sqlcode || sqlerrm;
     dbms_output.put_line(o_vc_return_flag);
return;
end;

/

注3：因为这个复合索引是varchar2和number类型字段的复合索引。所以这第二个index key需要用下面的方法取出来。

create or replace function undump_number(in_datalist in varchar2) return number is


v_regexp constant varchar2(10) := '[^ ]+';
v_tmnt   constant varchar2(1)  := ' ';
v_minus  constant number  := 193;
v_power  constant number  :=100;


v_ln number;
v_num number;
o_result number := 0;
v_datalist varchar2(100);


type type_character_char is table of varchar2(5) index by binary_integer;
characters_char type_character_char;


begin
  v_datalist := rtrim(in_datalist)||v_tmnt;
  v_num := length(translate(v_datalist,v_tmnt||v_datalist,v_tmnt));
  SELECT REGEXP_SUBSTR(v_datalist, v_regexp, 1, LEVEL) bulk collect into characters_char   FROM DUAL connect by level <=v_num;
  v_ln := to_number(characters_char(1),'xx')-v_minus;
  for i in 2..v_num loop
    o_result := o_result+(to_number(characters_char(i),'xx')-1)*power(v_power,v_ln-i+2);
  end loop;
  return o_result;
end;

/

到这里就结束了。