oracle B*树索引原理学习

    B*Tree索引是最常见的索引结构，默认建立的索引就是这种类型的索引。B*Tree索引在检索高基数数据列（高基数数据列是指该列有很多不同的值）时提供了最好的性能。当取出的行数占总行数比例较小时B-Tree索引比全表检索提供了更有效的方法。但当检查的范围超过表的10%时就不能提高取回数据的性能。B-Tree索引是基于二叉树的，由分支块（branch block）和叶块（leaf block）组成。在树结构中，位于最底层底块被称为叶块，包含每个被索引列的值和行所对应的rowid。在叶节点的上面是分支块，用来导航结构，包含了索引列（关键字）范围和另一索引块的地址。

   理想情况下，树的高度应该最小，因为为了获取某个信息而进行的读操作的次数与树的高度成正比。为了定位某个关键字，最少需要进行三个读操作（两个读操作发生在根/分支级，第三个读动作发生在叶节点级）。需要记住的是，根据索引所布局的方式不同，每个读操作都有可能是逻辑读或物理读。如果单独的某个读操作将所需要的块放置在内存中，那么后面的读操作将有可能直接从内存中进行；否则，它们将需要从硬盘中进行读。高度与阶数成反比，阶数越高，高度将越低（这种树称为“扁平”树）。在任何B*树的实现中，一个重要目标就是增加分支数、减少高度，从而保证对叶节点的访问被加速。

    在B *树的实现过程中，主要应该注意的问题是INSERT操作、DELETE操作与UPDATE操作。

   在每个INSERT操作过程中，关键字必须被插入在正确叶节点的位置。如果叶节点已满，不能容纳更多的关键字，就必须将叶节点拆分。拆分的方法有两种：

1)如果新关键字值在所有旧叶节点块的所有关键字中是最大的，那么所有的关键字将按照99:1的比例进行拆分，使得在新的叶节点块中只存放有新关键字，而其他的所有关键字（包括所有删除的关键字）仍然保存在旧叶节点块中。

2)如果新关键字值不是最大的，那么所有的关键字将按照50:50的比例进行拆分，这时每个叶节点块（旧与新）中将各包含原始叶节点中的一半关键字。

    这个拆分必须通过一个指向新叶节点的新入口向上传送到父节点。如果父节点已满，那么这个父节点也必须进行拆分，并且需要将这种拆分向上传送到父节点的父节点。这时，如果这个父节点也已满，将继续进行这个过程。这样，某个拆分可能最终被一直传送到根节点。如果根节点满了，根结点也将进行分裂。根结点在进行分裂的时候，就是树的高度增加的时候。根节点进行分裂的方式跟其他的的节点分裂的方式相比较，在物理位置上的处理也是不同的。根节点分裂时，将原来的根结点分裂为分支节点或叶节点，保存到新的块中，而将新的根节点信息保存到原来的根结点块中，这样做的是为因为避免修改数据字典所带来的相对较大的开销。

    在索引的每一个层次之间，每一个层最左边的节点的block头部都有一个 指向下层最左边的块的指针，这样有利于 fast full scan 的快速定位最左边的叶子节点。

   每个拆分过程都是要花费一定的开销的，特别是要进行物理硬盘I/O动作。此外，在进行拆分之前，Oracle必须查找到一个空块，用来保存这个拆分。可以用以下步骤来进行查找空块的动作：

1) 在索引的自由列表(free-list, 又称为空闲列表) 中查到一个空闲块，可以通过CREATE/ALTER INDEX命令为一个索引定义多个空闲列表。索引空闲列表并不能帮助Oracle查找一个可用来存放将要被插入的新关键字的块。这是因为关键字值不能随机地存放在索引中可用的第一个“空闲”叶节点块中，这个值必须经过适当的排序之后，放置在某个特定的叶节点块中。只有在块拆分过程中才需要使用索引的空闲列表，每个空闲列表都包含有一个关于“空”块的链接列表。当为某个索引定义了多个空闲列表时，首先将从分配给进程的空间列表中扫描一个空闲块。如果没有找到所需要的空闲块，将从主空闲列表中进行扫描空闲块的动作。

2) 如果没有找到任何空闲块，Oracle将试图分配另一个扩展段。如果在表空间中没有更多的自由空间，Oracle将产生错误ORA-01654。

3) 如果通过上述步骤，找到了所需的空闲块，那么这个索引的高水位标(HWM)将加大。

4) 所找到的空闲块将用来执行拆分动作。

    在创建B*树索引时，一个需要注意的问题就是要避免在运行时进行拆分，或者，要在索引创建过程中进行拆分（“预拆分”），从而使得在进行拆分时能够快速命中，以便避免运行时插入动作。当然，这些拆分也不仅仅局限于插入动作，在进行更新的过程中也有可能会发生拆分动作。

下面来分析一下在Oracle中进行已索引关键字的UPDATE动作时，会发生什么事情。

    索引更新完全不同于表更新，在表更新中，数据是在数据块内部改变的（假设数据块中有足够的空间来允许进行这种改变）；但在索引更新中，如果有关键字发生改变，那么它在树中的位置也需要发生改变。请记住，一个关键字在B *树中有且只有一个位置。因此，当某个关键字

发生改变时，关键字的旧表项必须被删除，并且需要在一个新的叶节点上创建一个新的关键字。旧的表项有可能永远不会被重新使用，这是因为只有在非常特殊的情况下， Oracle才会重用关键字表项槽，例如，新插入的关键字正好是被删除的那个关键字（包括数据类型、长度

等等）。（这里重用的是块，但完全插入相同的值的时候，也不一定插入在原来的被删除的位置，只是插入在原来的块中，可能是该块中的一个新位置。也正因为如此，在索引块中保存的的记录可能并不是根据关键字顺序排列的，随着update等的操作，会发生变化。）那么，这种情况发生的可能性有多大呢？许多应用程序使用一个数列来产生NUMBER关键字（特别是主关键字）。除非它们使用了RECYCLE选项，否则这个数列将不会两次产生完全相同的数。这样，索引中被删除的空间一直没有被使用。这就是在大规模删除与更新过程中，表大小不断减小或至少保持不变但索引不断加大的原因。

通过上面对B *树的分析，可以得出以下的应用准则：

1）避免对那些可能会产生很高的更新动作的列进行索引。

2）避免对那些经常会被删除的表中的多个列进行索引。若有可能，只对那些在这样的表上会进行删除的主关键字与/或列进行索引。如果对多个列进行索引是不可避免的，那么就应该考虑根据这些列对表进行划分，然后在每个这样的划分上执行TRUNCATE动作（而不是DELETE动作）。TRUNCATE在与DROP STORAGE短语一同使用时，通过重新设置高水位标来模拟删除表与索引以及重新创建表与索引的过程。

3）避免为那些唯一度不高的列创建B*树索引。这样的低选择性将会导致树节点块的稠密性，从而导致由于索引“平铺( flat)”而出现的大规模索引扫描。唯一性的程度越高，性能就越好，因为这样能够减少范围扫描，甚至可能用唯一扫描来取代范围扫描。

4）空值不应该存储在单列索引中。对于复合索引的方式，只有当某个列不空时，才需要进行值的存储。在为DML语句创建IS NULL或IS NOT NULL短语时，应该切记这个问题。

5）IS NULL不会导致索引扫描，而一个没有带任何限制的IS NOT NULL则可能会导致完全索引扫描。

2. PCTFREE的重要性

    对于B*树索引， PCTFREE可以决定叶节点拆分的extent。也就是说，PCTFREE用来说明在某个块中的自由空间数目，以便于后来的更新动作。但是，对于索引（与表不同），这些更新动作没有任何意义，因为更新会删除一个索引，然后又出现插入一个新索引。

    对于索引，PCTFREE大多数是在索引创建过程中发生作用，可以用一个非零值来说明块拆分比例。如果在索引创建过程中，PCTFREE被设置为20，那么有80％的叶节点将可能会包含关键字信息。但是，剩余的20％将用来作为关键字信息后来插入到叶节点块中时使用。这样将能够保证在需要进行叶节点块的拆分时，运行时的插入开销最小。虽然一个较高的PCTFREE可能会使得索引创建时间增加，但它能够防止在实际的使用过程中命中性能的降低。因此，那些正在等待某个行被插入的终端用户并不会因为需要进行叶节点块的拆分而使得自己的性能受到影响。

基于上述信息，可以得出以下结论：

1）某个索引的PCTFREE主要是在索引创建时使用。在实际的应用过程中，PCTFREE将被忽略。

2）如果表是一个经常被访问、包含有大量DML改变（通过交互式用户屏幕）的表，那么就应该为OLTP应用程序指定一个较高的PCTFREE。

3）如果索引创建时间很关键，那么就应该指定一个较低PCTFREE。这样在每个叶节点块中将会压缩有多个行，从而可以避免在索引创建时进行更多的拆分。这一点对于2 4×7客户站点非常重要，因为在大多数情况下索引创建过程需要很多的系统停工时间（特别是在表有几百万行时更为如此）。

4）对于任何其值不断增加的列，最好是设置一个非常低的PCTFREE（甚至可以为0）。这是因为只有那些最右方的叶节点块总是会被插入，从而使得树向右增长。而左边的叶节点将一直为静止状态，因此没有必要使得这些块的任何部分为空（通过使用非零PCTFREE）。

索引的实验：

SQL> create table test(id number,name varchar2(100));

表已创建

SQL> create index idx_test on test(id);

索引已创建

SQL>  select file_id,extent_id,block_id from dba_extents where segment_name='IDX_TEST' ;

   FILE_ID  EXTENT_ID   BLOCK_ID

---------- ---------- ----------

         6          0         41

此时索引为空，我们来转储一下41号块的信息

SQL>  alter system dump datafile 6 block 41;

系统已更改。

可以通过以下的方法得到转储的数据文件

在Oracle Database 11g之前，要想获得跟踪文件的名称，通常我们需要执行一系列的查询，常用的脚本如下:

SQL> SELECT a.VALUE || b.symbol || c.instance_name || '_ora_' || d.spid || '.trc'

  2            trace_file

  3    FROM (SELECT VALUE

  4            FROM v$parameter

  5           WHERE NAME = 'user_dump_dest') a,

  6         (SELECT SUBSTR (VALUE, -6, 1) symbol

  7            FROM v$parameter

  8           WHERE NAME = 'user_dump_dest') b,

  9         (SELECT instance_name FROM v$instance) c,

 10         (SELECT spid

 11            FROM v$session s, v$process p, v$mystat m

 12           WHERE s.paddr = p.addr AND s.SID = m.SID AND m.statistic# = 0) d

 13  /

TRACE_FILE

--------------------------------------------------------------------------------

F:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP\orcl_ora_10076.trc

11g使用如下脚本，得到转储文件

SQL> SELECT VALUE FROM V$DIAG_INFO WHERE NAME = "Default Trace File";

VALUE

-------------------------------------------------------------------------------

F:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP\orcl_ora_10076.trc

既然已经找到了转储文件，那就看看转储文件的内容：

Start dump data blocks tsn: 7 file#: 6 minblk 41 maxblk 41  --转储的文件号和块号

buffer tsn: 7 rdba: 0x01800029 (6/41)

scn: 0x0000.000bddf9 seq: 0x01 flg: 0x04 tail: 0xddf92001

frmt: 0x02 chkval: 0x4b0e type: 0x20=FIRST LEVEL BITMAP BLOCK --这个表示它是一级位图块，直接和数据块打交道

Hex dump of block: st=0, typ_found=1

Dump of First Level Bitmap Block

 --------------------------------

   nbits : 2 nranges: 1         parent dba:  0x0180002a   poffset: 0      --标红的表示它的父级位图块

   unformatted: 12      total: 16        first useful block: 3      

   owning instance : 1

   instance ownership changed at 04/20/2013 12:08:33

   Last successful Search 04/20/2013 12:08:33

   Freeness Status:  nf1 0      nf2 1      nf3 0      nf4 0      

 

   Extent Map Block Offset: 4294967295 

   First free datablock : 3      

   Bitmap block lock opcode 0

   Locker xid:     :  0x0000.000.00000000

   Inc #: 0 Objd: 52590 

  HWM Flag: HWM Set

      Highwater::  0x0180002d  ext#: 0      blk#: 4      ext size: 16    

  #blocks in seg. hdr's freelists: 0     

  #blocks below: 1     

  mapblk  0x00000000  offset: 0     

  --------------------------------------------------------

  DBA Ranges :

  --------------------------------------------------------

   0x01800029  Length: 16     Offset: 0      

   0:Metadata   1:Metadata   2:Metadata   3:25-50% free 

   4:unformatted   5:unformatted   6:unformatted   7:unformatted 

   8:unformatted   9:unformatted   10:unformatted   11:unformatted

   12:unformatted   13:unformatted   14:unformatted   15:unformatted

  --------------------------------------------------------

End dump data blocks tsn: 7 file#: 6 minblk 41 maxblk 41

-标红的这些块被用来存储元数据，其余的块用来存储数据，注意到总共有16个块，full表示数据块已经用完了。

如果这些块用完的话，oracle就会使用二级位图结构，以此类推，会出现三级位图结构，oracle目前支持到三级位图结构，上面的表示这些块暂时未被使用，根据以上的说明oracle会从第4个块（44）开始存放索引，索引的branch_code应该放在第44块中，接下来我们就分析索引是如何分布的。

下面是一个rdba转换为具体的文件和块号的方法，后面会用到。

create or replace function getbfno(p_dba in varchar2)

  return varchar2

 is

 l_str varchar2(255) default null; 

 begin 

   l_str :='datafile# is:'

   ||dbms_utility.data_block_address_file(TO_NUMBER(LTRIM(P_DBA,'0x'),'xxxxxxxx'))

   ||chr(10) ||'datablock is:'

   ||dbms_utility.data_block_address_block(TO_NUMBER(LTRIM(P_DBA,'0x'),'xxxxxxxx'));

    return l_str;

   end;

--去上面的rdba得到如下的转换

SQL> select getbfno('0x01800029') from dual;

GETBFNO('0X01800029')

---------------------------------------------

datafile# is:6

datablock is:41

在开始插入索引前，我们下去看看  parent dba:  0x0180002a 这个里面有什么东西，也就是是二级位图块。

SQL> select getbfno('0x0180002a') from dual;

GETBFNO('0X0180002A')

-------------------------------------------------

datafile# is:6