Oracle的B_Tree索引
B树索引的相关概念
索引与表一样，也属于段（segment）的一种。里面存放了用户的数据，跟表一样需要占用磁盘空间。只不过，在索引里的数据存放形式与表里的数据存放形式非常的不一样。在理解索引时，可以想象一本书，其中书的内容就相当于表里的数据，而书前面的目录就相当于该表的索引。同时，通常情况下，索引所占用的磁盘空间要比表要小的多，其主要作用是为了加快对数据的搜索速度，也可以用来保证数据的唯一性。
但是，索引作为一种可选的数据结构，你可以选择为某个表里的创建索引，也可以不创建。这是因为一旦创建了索引，就意味着oracle对表进行DML（包括INSERT、UPDATE、DELETE）时，必须处理额外的工作量（也就是对索引结构的维护）以及存储方面的开销。所以创建索引时，需要考虑创建索引所带来的查询性能方面的提高，与引起的额外的开销相比，是否值得。
从物理上说，索引通常可以分为：分区和非分区索引、常规B树索引、位图（bitmap）索引、翻转（reverse）索引等。其中，B树索引属于最常见的索引，由于我们的这篇文章主要就是对B树索引所做的探讨，因此下面只要说到索引，都是指B树索引。
B树索引是一个典型的树结构，其包含的组件主要是：
1) 叶子节点（Leaf node）：包含条目直接指向表里的数据行。
2) 分支节点（Branch node）：包含的条目指向索引里其他的分支节点或者是叶子节点。
3) 根节点（Root node）：一个B树索引只有一个根节点，它实际就是位于树的最顶端的分支节点。
可以用下图一来描述B树索引的结构。其中，B表示分支节点，而L表示叶子节点。
对于分支节点块（包括根节点块）来说，其所包含的索引条目都是按照顺序排列的（缺省是升序排列，也可以在创建索引时指定为降序排列）。每个索引条目（也可以 叫做每条记录）都具有两个字段。第一个字段表示当前该分支节点块下面所链接的索引块中所包含的最小键值；第二个字段为四个字节，表示所链接的索引块的地址，该地址指向下面一个索引块。在一个分支节点块中所能容纳的记录行数由数据块大小以及索引键值的长度决定。比如从上图一可以看到，对于根节点块来说，包含三条记录，分别为（0 B1）、（500 B2）、（1000 B3），它们指向三个分支节点块。其中的0、500和1000分别表示这三个分支节点块所链接的键值的最小值。而B1、B2和B3则表示所指向的三个分支节点块的地址。
对于叶子节点块来说，其所包含的索引条目与分支节点一样，都是按照顺序排列的（缺省是升序排列，也可以在创建索引时指定为降序排列）。每个索引条目（也可以叫做每条记录）也具有两个字段。第一个字段表示索引的键值，对于单列索引来说是一个值；而对于多列索引来说则是多个值组合在一起的。第二个字段表示键值所 对应的记录行的ROWID，该ROWID是记录行在表里的物理地址。如果索引是创建在非分区表上或者索引是分区表上的本地索引的话，则该ROWID占用6个字节；如果索引是创建在分区表上的全局索引的话，则该ROWID占用10个字节。
知道这些信息以后，我们可以举个例子来说明如何估算每个索引能够包含多少条目，以及对于表来说，所产生的索引大约多大。对于每个索引块来说，缺省的PCTFREE为10％，也就是说最多只能使用其中的90％。同时9i以后，这90％中也不可能用尽，只能使用其中的87％左右。也就是说，8KB的数据块中能够实际用来存放索引数据的空间大约为6488（8192×90％×88％）个字节。
假设我们有一个非分区表，表名为warehouse，其数据行数为130万行。该表中有一个列，列名为goodid，其类型为char（8），那么也就是说该goodid的长度为固定值：8。同时在该列上创建了一个B树索引。
在叶子节点中，每个索引条目都会在数据块中占一行空间。每一行用2到3个字节作为行头，行头用来存放标记以及锁定类型等信息。同时，在第一个表示索引的键值的字段中，每一个索引列都有1个字节表示数据长度，后面则是该列具体的值。那么对于本例来说，在叶子节点中的一行所包含的数据大致如下图二所示：
叶子节点刨面图（图2）
从上图可以看到，在本例的叶子节点中，一个索引条目占18个字节。同时我们知道8KB的数据块中真正可以用来存放索引条目的空间为6488字节，那么在本例中，一个数据块中大约可以放360（6488/18）个索引条目。而对于我们表中的130万条记录来说，则需要大约3611（1300000/360）个叶子节点块。
而对于分支节点里的一个条目（一行）来说，由于它只需保存所链接的其他索引块的地址即可，而不需要保存具体的数据行在哪里，因此它所占用的空间要比叶子节点要少。分支节点的一行中所存放的所链接的最小键值所需空间与上面所描述的叶子节点相同；而存放的索引块的地址只需要4个字节，比叶子节点中所存放的ROWID少了2个字节，少的这2个字节也就是ROWID中用来描述在数据块中的行号所需的空间。因此，本例中在分支节点中的一行所包含的数据大致如下图三所示：
分支节点刨面图（图3）
从上图可以看到，在本例的分支节点中，一个索引条目占16个字节。根据上面叶子节点相同的方式，我们可以知道一个分支索引块可以存放大约405（6488/16）个索引条目。而对于我们所需要的3611个叶子节点来说，则总共需要大约9个分支索引块。
这样，我们就知道了我们的这个索引有2层，第一层为1个根节点，第二层为9个分支节点，而叶子节点数为3611个，所指向的表的行数为1300000行。但是要注意，在oracle的索引中，层级号是倒过来的，也就是说假设某个索引有N层，则根节点的层级号为N，而根节点下一层的分支节点的层级号为N-1，依此类推。对本例来说，9个分支节点所在的层级号为1，而根节点所在的层级号为2。
我们可以使用如下方式将B树索引转储成树状结构的形式而呈现出来：
比如，对于上面的例子来说，我们把创建在goodid上的名为idx_warecountd_goodid的索引转储出来。
select object_id from user_objects where object_name='索引名';
OBJECT_ID
7378
SQL> alter session set events 'immediate trace name treedump level 7378';
打开转储出来的文件以后，我们可以看到类似下面的内容：
路径如下：E:\Dev\oracle\product\10.2.0\db_1\admin\bbdreams\udump；
----- begin tree dump
branch: 0x180eb0a 25225994 (0: nrow: 9, level: 2)
branch: 0x180eca1 25226401 (-1: nrow: 405, level: 1)
leaf: 0x180eb0b 25225995 (-1: nrow: 359 rrow: 359)
leaf: 0x180eb0c 25225996 (0: nrow: 359 rrow: 359)
leaf: 0x180eb0d 25225997 (1: nrow: 359 rrow: 359)
leaf: 0x180eb0e 25225998 (2: nrow: 359 rrow: 359)
…………………
branch: 0x180ee38 25226808 (0: nrow: 406, level: 1)
leaf: 0x180eca0 25226400 (-1: nrow: 359 rrow: 359)
leaf: 0x180eca2 25226402 (0: nrow: 359 rrow: 359)
leaf: 0x180eca3 25226403 (1: nrow: 359 rrow: 359)
leaf: 0x180eca4 25226404 (2: nrow: 359 rrow: 359)
…………………
其中，每一行的第一列表示节点类型：branch表示分支节点（包括根节点），而leaf则表示叶子节点；第二列表示十六进制表示的节点的地址；第三列表示十进制表示的节点的地址；第四列表示相对于前一个节点的位置，根节点从0开始计算，其他分支节点和叶子节点从-1开始计算；第五列的nrow表示当前节点中所含有的索引条目的数量。比如我们可以看到根节点中含有的nrow为9，表示根节点中含有9个索引条目，分别指向9个分支节点；第六列中的level表示分支节点的层级，对于叶子节点来说level都是0。第六列中的rrow表示有效的索引条目（因为索引条目如果被删除，不会立即被清除出索引块中。所以nrow减rrow的数量就表示已经被删除的索引条目数量）的数量，比如对于第一个leaf来说，其rrow为359，也就是说该叶子节点中存放了359个可用索引条目，分别指向表warecountd的359条记录。
上面这种方式以树状形式转储整个索引。同时，我们可以转储一个索引节点来看看其中存放了些什么。转储的方式为：
alter system dump datafile file# block block#;
我们从上面转储结果中的第二行知道，索引的根节点的地址为25225994，因此我们先将其转换为文件号以及数据块号。
SQL> select dbms_utility.data_block_address_file(25225994),
2 dbms_utility.data_block_address_block(25225994) from dual;
DBMS_UTILITY.DATA_BLOCK_ADDRES DBMS_UTILITY.DATA_BLOCK_ADDRES
------------------------------ ------------------------------
6 60170
于是，我们转储根节点的内容。
SQL> alter system dump datafile 6 block 60170;
打开转储出来的跟踪文件，我们可以看到如下的索引头部的内容：
header address 85594180=0x51a1044
kdxcolev 2
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: pcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 8
kdxcofbo 44=0x2c
kdxcofeo 7918=0x1eee
kdxcoavs 7874
kdxbrlmc 25226401=0x180eca1
kdxbrsno 0
kdxbrbksz 8060
其中的kdxcolev表示索引层级号，这里由于我们转储的是根节点，所以其层级号为2。对叶子节点来说该值为0；kdxcolok表示该索引上是否正在发生修改块结构的事务；kdxcoopc表示内部操作代码；kdxconco表示索引条目中列的数量；kdxcosdc表示索引结构发生变化的数量，当你修改表里的某个索引键值时，该值增加；kdxconro表示当前索引节点中索引条目的数量，但是注意，不包括kdxbrlmc指针；kdxcofbo表示当前索引节点中可用空间的起始点相对当前块的位移量；kdxcofeo表示当前索引节点中可用空间的最尾端的相对当前块的位移量；kdxcoavs表示当前索引块中的可用空间总量，也就是用kdxcofeo减去kdxcofbo得到的。kdxbrlmc表示分支节点的地址，该分支节点存放了索引键值小于row#0（在转储文档后半部分显示）所含有的最小值的所有节点信息；kdxbrsno表示最后一个被修改的索引条目号，这里看到是0，表示该索引是新建的索引；kdxbrbksz表示可用数据块的空间大小。实际从这里已经可以看到，即便是PCTFREE设置为0，也不能用足8192字节。
再往下可以看到如下的内容。这部分内容就是在根节点中所记录的索引条目，总共是8个条目。再加上
row#0[8043] dba: 25226808=0x180ee38
col 0; len 8; (8): 31 30 30 30 30 33 39 32
col 1; len 3; (3): 01 40 1a
……
row#7[7918] dba: 25229599=0x180f91f
col 0; len 8; (8): 31 30 30 31 31 32 30 33
col 1; len 4; (4): 01 40 8f a5
kdxbrlmc所指向的第一个分支节点，我们知道该根节点中总共存放了9个分支节点的索引条目，而这正是我们在前面所指出的为了管理3611个叶子节点，我们需要9个分支节点。
每个索引条目都指向一个分支节点。其中col 1表示所链接的分支节点的地址，该值经过一定的转换以后实际就是row#所在行的dba的值。如果根节点下没有其他的分支节点，则col 1为TERM；col 0表示该分支节点所链接的最小键值。其转换方式非常复杂，比如对于row #0来说，col 0为31 30 30 30 30 30 30 33，则将其中每对值都使用函数to_number(NN,’XX’)的方式从十六进制转换为十进制，于是我们得到转换后的值：49,48,48,48,48,48,48,51，因为我们已经知道索引键值是char类型的，所以对每个值都运用chr函数就可以得到被索引键值为：10000003。实际上，对10000003运用dump函数得到的结果就是：49,48,48,48,48,48,48,51。所以我们也就知道，10000003就是dba为25226808的索引块所链接的最小键值。
SQL> select dump('10000003') from dual;
DUMP('10000003')
-------------------------------------
Typ=96 Len=8: 49,48,48,48,48,48,48,50
接下来，我们从根节点中随便找一个分支节点，假设就是row#0所描述的25226808。对其运用前面所介绍过的dbms_utility里的存储过程获得其文件号和数据块号，并对该数据块进行转储，其内容如下所示。可以
row#0[8043] dba: 25226402=0x180eca2
col 0; len 8; (8): 31 30 30 30 30 33 39 33
col 1; len 3; (3): 01 40 2e
………
row#404[853] dba: 25226806=0x180ee36
col 0; len 8; (8): 31 30 30 30 31 36 34 30
col 1; len 3; (3): 01 40 09
----- end of branch block dump -----
发现内容与根节点完全类似，只不过该索引块中所包含的索引条目（指向叶子节点）的数量更多了，为405个。这也与我们前面所说的一个分支索引块可以存放大约405（6488/16）个索引条目完全一致。
然后，我们从中随便挑一个叶子节点，对其进行转储。假设就选row#0行所指向的叶子节点，根据dba的值：25226402可以知道，文件号为6，数据块号为60578。将其转储以后，其内容如下所示，我只显示与分支节点不同的部分。
………
kdxlespl 0
kdxlende 0
kdxlenxt 25226403=0x180eca3
kdxleprv 25226400=0x180eca0
kdxledsz 0
kdxlebksz 8036
其中的kdxlespl表示当叶子节点被拆分时未提交的事务数量；kdxlende表示被删除的索引条目的数量；kdxlenxt表示当前叶子节点的下一个叶子节点的地址；kdxlprv表示当前叶子节点的上一个叶子节点的地址；kdxledsz表示可用空间，目前是0。
转储文件中接下来的部分就是索引条目部分，每个条目包含一个ROWID，指向一个表里的数据行。如下所示。其中flag表示标记，比如删除标记等；而lock表示锁定信息。col 0表示索引键值，其算法与我们在前面介绍分支节点时所说的算法一致。col 1表示ROWID。我们同样可以看到，该叶子节点中包含了359个索引条目，与我们前面所估计的一个叶子节点中大约可以放360个索引条目也是基本一致的。
row#0[8018] flag: -----, lock: 0
col 0; len 8; (8): 31 30 30 30 30 33 39 33
col 1; len 6; (6): 01 40 2e 93 00 16
row#1[8000] flag: -----, lock: 0
col 0; len 8; (8): 31 30 30 30 30 33 39 33
col 1; len 6; (6): 01 40 2e e7 00 0e
…………
row#358[1574] flag: -----, lock: 0
col 0; len 8; (8): 31 30 30 30 30 33 39 37
col 1; len 6; (6): 01 40 18 ba 00 1f
----- end of leaf block dump -----
