9.2 索引
9.2.1 B树(Balance Tree)索引
1. 索引与表一样也属于一种段，存放了用户数据，需要磁盘空间，只是存放形式
与表中不一样，索引是一个有序排列的结构 。 虽然创建索引对于查询有好处，
但是创建索引后对于insert,update, delete 操作而言，增加了额外的工作量，
他们在操作时需要对索引结构进行维护，所以创建索引时要权衡查询与DML及
存储空间等各方面的问题。
2. 索引分类
从物理上来看，索引可以分为分区和非分区索引，常规B-Tree索引，位图(Bitmap)
索引，反转(Reverse)索引，其中B-Tree是最常见的索引。本章中提到的索引，没有
特别申明，都是指b-tree索引。
3. B-Tree索引结构
叶子节点(leaf node): 包含的条目中具有指针，指向表中的数据行，叶子节点之间
相互指向。只要表中记录行中被索引的列的值不为空，就会在索引叶子节点里面存在
一个对应的条目，如果被索引的列为空，那么在叶子节点中不会存在对应的条目。
分支节点(branch node): 包含的条目指向索引里其他的分支节点或叶子节点。
根节点(root node): 一个b-tree索引只有一个根节点，本质来讲，它就是位于
树顶端的分支节点。
备注： 索引条目 -- 索引数据块中的数据行(包含部分数据及索引块地址)。
对于分支节点块(branch node), 包括根节点块，所包含的索引条目都是按照顺序排
列的(默认是升序排列的，当然也可以在建索引时指定为降序)，每个索引条目(也可
以叫做每条记录)都有两个字段，第一个字段表示当前该分支节点下所链接的索引块中
所包含的最小键值 (键值：也就是索引所在列的值)； 第二个字段为四个字节，表示
所链接的索引块的地址，该地址指向下面其所链接的索引块。
注意：这里的所谓索引块，分支节点块或叶子节点块就是Oracle物理block。
每个节点块=oracle block size. 在一个分支节点块中所能容纳的记录行数
由数据块大小(即Oracle Block Size)以及索引键值(索引所在列的值)的长度决定。
例子图片见
http://space.itpub.net/batch.download.php?aid=4959
从图中可以看到地址部分B1,B2,B3表示对应branch,L1,L2,L3表示对应leaf,而R1,R2,
R3等表示对应数据行rowid .
对于叶子节点块来说，其所包含的索引条目与分支节点一样，都是按照顺序排列的
(默认升序，也可在创建时指定为降序)，每个索引条目(也可以叫做每条记录)也具
有两个字段。第一个字段表示索引的键值(索引所在列的值)，对于单列索引来说是
一个值，对于组合索引而言是多个值组合在一起的。 第二个字段表示键值所对应
的记录行的ROWID, 该ROWID是记录行在表中的物理地址。如果索引是创建在非分区
表上或者索引是分区表上的本地索引的话，那么ROWID占用6个字节； 如果是创建
在分区表上的全局索引的话，rowid占用10个字节。
了解这些之后，我们来说明如何估算每个索引能占用多少条目，以及对于表来说，
所产生的索引大约多大，
比如对于一个索引块来说，缺省的pctfree是10%, 在9i以后，剩下的90%也只能
使用其中的87%, 也就是说8KB的oracle block能实际用来存放索引数据的空间大
约为 8KB * 90% * 87% = 6488字节。
假设一个非分区表，表名warecountd, 数据行数是130万，一个列goodid类型为
char(8),是固定长度，在该列上创建了一个B-Tree索引。
在叶子节点中，每个索引条目都会在索引数据块中占一行空间。每一行用2到3
个字节作为行头，行头用来存放标记以及锁定类型等信息，同时在第一个表示
索引的键值的字段中，每一个索引列都有一个字节表示数据长度，后面就是该
列具体的值，那么对于本例来说，在叶子节点中的一行所包含的数据大致如下：
------------------------------------------------------------------------------------
行头(2字节) | C1列的长度(1字节) | C1列的值(8字节) | rowid长度(1字节) | rowid (6字节)
------------------------------------------------------------------------------------
可以看出，本例的叶子节点，索引条目占18个字节，8K Block中能用的空间为
6488字节，那么本例中，一个索引数据块可以放6488/18=360个索引条目，而对
于130万条记录(约130个索引条目，因为可能goodi列有些行的值为空)，则需要
大约 1300000/360 = 3611 个叶子节点块 。
对于分支节点中的一个条目来说，因为它只需要保存所链接的其他索引块的地址
即可，而不需要保存具体的数据行在哪里，因此它占用的空间要比叶子节点要少。
分支节点中一行中所存放的链接的最小键值所需空间与上面所描述的叶子节点相
同，而存放索引块的地址只需要4个字节，比叶子节点存放rowid少了2个字节，少
的这两个字节也就是rowid中用来描述在数据块中行号所需的空间，因此，本例中
在分支节点中的一行包含的数据大致如下：
-----------------------------------------------------------------------------------------------
行头(2字节) | C1列的长度(1字节) | C1列的值(8字节) | file#+block#长度(1字节) | file#+block#(4字节)
-----------------------------------------------------------------------------------------------
由上可以知道，分支节点中一个索引条目占16个字节，可以知道一个分支索引块
可以存放大概6488/16=405个索引条目。而对于我们需要的3611个叶子节点来说，
则共需要 3611/405=9个分支索引块 。
这样，我们就知道了这个索引有两层，第一层是1个根节点，第二层是9个分支节
点，叶子节点数为3611个，指向的数据行为130万，不过Oracle中层级号是倒过
来的，根节点为N, 下一层分支节点层级号是N-1. 对于本例，根节点层级号为2，
9个分支节点层级号为1 .
4. B-Tree索引的内部结构
我们可以用如下方式将B-Tree索引dump成树状结构的形式呈现出来：
alter session set events 'immediate trace name treedump level INDEX_OBJECT_ID';
例子：
select object_id from user_objects where object_name='EFOXSFCEDIASBUILDCHILD_PK' ;
OBJECT_ID
-----------
51811
SQL> alter session set events 'immediate trace name treedump level 51811' ;
## 以下我们加上linux行号
21 ----- begin tree dump
22 branch: 0x280070c 41944844 (0: nrow: 3, level: 3)
23 branch: 0x28179f7 42039799 (-1: nrow: 154, level: 2)
24 branch: 0x240a9be 37792190 (-1: nrow: 109, level: 1)
25 leaf: 0x2800713 41944851 (-1: nrow: 55 rrow: 55)
26 leaf: 0x280076f 41944943 (0: nrow: 54 rrow: 54)
27 leaf: 0x2800770 41944944 (1: nrow: 51 rrow: 51)
28 leaf: 0x280076a 41944938 (2: nrow: 48 rrow: 48)
29 leaf: 0x2800711 41944849 (3: nrow: 51 rrow: 51)
30 leaf: 0x2800778 41944952 (4: nrow: 54 rrow: 54)
31 leaf: 0x2800771 41944945 (5: nrow: 67 rrow: 67)
32 leaf: 0x2800772 41944946 (6: nrow: 67 rrow: 67)
33 leaf: 0x2800773 41944947 (7: nrow: 61 rrow: 61)
34 leaf: 0x2800774 41944948 (8: nrow: 68 rrow: 68)
35 leaf: 0x2800775 41944949 (9: nrow: 61 rrow: 61)
36 leaf: 0x2800776 41944950 (10: nrow: 70 rrow: 70)
37 leaf: 0x2800777 41944951 (11: nrow: 68 rrow: 68)
38 leaf: 0x280077b 41944955 (12: nrow: 67 rrow: 67)
39 leaf: 0x280077c 41944956 (13: nrow: 52 rrow: 52)
......
129 leaf: 0x240a9b6 37792182 (103: nrow: 68 rrow: 68)
130 leaf: 0x240a9c2 37792194 (104: nrow: 67 rrow: 67)
131 leaf: 0x240a9c6 37792198 (105: nrow: 52 rrow: 52)
132 leaf: 0x240a98f 37792143 (106: nrow: 68 rrow: 68)
133 leaf: 0x240a98b 37792139 (107: nrow: 65 rrow: 65)
134 branch: 0x280f386 42005382 (0: nrow: 113, level: 1)
135 leaf: 0x240a993 37792147 (-1: nrow: 68 rrow: 68)
136 leaf: 0x240a99b 37792155 (0: nrow: 60 rrow: 60)
137 leaf: 0x240a99f 37792159 (1: nrow: 66 rrow: 66)
138 leaf: 0x240a9a3 37792163 (2: nrow: 60 rrow: 60)
139 leaf: 0x240a9a7 37792167 (3: nrow: 66 rrow: 66)
.......
245 leaf: 0x280f32e 42005294 (109: nrow: 68 rrow: 68)
246 leaf: 0x280f32a 42005290 (110: nrow: 67 rrow: 67)
247 leaf: 0x280f332 42005298 (111: nrow: 52 rrow: 52)
248 branch: 0x240aa85 37792389 (1: nrow: 100, level: 1)
249 leaf: 0x280f336 42005302 (-1: nrow: 68 rrow: 68)
250 leaf: 0x280f33e 42005310 (0: nrow: 65 rrow: 65)
251 leaf: 0x280f33a 42005306 (1: nrow: 68 rrow: 68)
........
458 leaf: 0x280f3da 42005466 (107: nrow: 65 rrow: 65)
459 branch: 0x280f3b9 42005433 (3: nrow: 113, level: 1)
460 leaf: 0x280f3de 42005470 (-1: nrow: 68 rrow: 68)
461 leaf: 0x280f3e2 42005474 (0: nrow: 60 rrow: 60)
........
16382 leaf: 0x2816324 42033956 (89: nrow: 68 rrow: 68)
16383 leaf: 0x2816334 42033972 (90: nrow: 67 rrow: 67)
16384 leaf: 0x2816344 42033988 (91: nrow: 52 rrow: 52)
16385 branch: 0x2817a07 42039815 (0: nrow: 156, level: 2)
16386 branch: 0x281603d 42033213 (-1: nrow: 100, level: 1)
16387 leaf: 0x2816354 42034004 (-1: nrow: 68 rrow: 68)
16388 leaf: 0x2816364 42034020 (0: nrow: 65 rrow: 65)
16389 leaf: 0x2816374 42034036 (1: nrow: 68 rrow: 68)
........
32602 leaf: 0x2818350 42042192 (89: nrow: 66 rrow: 66)
32603 leaf: 0x2818360 42042208 (90: nrow: 68 rrow: 68)
32604 branch: 0x2819da1 42048929 (1: nrow: 231, level: 2)
32605 branch: 0x28183f3 42042355 (-1: nrow: 109, level: 1)
32606 leaf: 0x2818370 42042224 (-1: nrow: 69 rrow: 69)
32607 leaf: 0x2818380 42042240 (0: nrow: 64 rrow: 64)
其中，第一列表示节点类型，branch表示分支节点(包括根节点)，leaf表示叶子
节点，第二列表示16进制的节点地址； 第三列表示十进制的节点地址；第四列表
示相对于前一个节点的位置，根节点从0开始计算，其他分支节点和叶子节点从-1
开始计算； 第五列的 nrow: 9 表示当前节点所含索引条目的数量，nrow: 9表示
根节点中含有9个索引条目，分别指向9个分支节点； 第六个列中的level表示分
支节点的层级，对于叶子节点level都是0， 第六列中rrow表示有效的索引条目数量
(因为索引条目如果被删除，不会立即被清除出索引块。所以nrow减rrow的数量就
表示已经被删除的索引条目数量)，
备注： 比如16进制280f3b9通过计算器转换为十进制42005433 。
同时我们还可以转储一个索引节点(块)来看看其中存放了什么，转储方式为：
alter system dump datafile file# block block# ;
从上面的trc文件可以知道，索引的根节点的地址是 41944844 . 我们先将其
转换为文件号和数据块号得到 file#=10, block#=1804 .
select dbms_utility.data_block_address_file(41944844),
dbms_utility.data_block_address_block(41944844) from dual ;
然后执行：
alter system dump datafile 10 block 1804 ;
从转储trc文件中可以看到如下索引头部的内容：
Branch block dump
=================
header address 216753740=0xceb664c
kdxcolev 3
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: pcode=0: iot flags=--- is converted=Y
kdxconco 3
kdxcosdc 3
kdxconro 2
kdxcofbo 32=0x20
kdxcofeo 8005=0x1f45
kdxcoavs 7973
kdxbrlmc 42039799=0x28179f7
kdxbrsno 1
kdxbrbksz 8056
kdxbr2urrc 0
row#0[8030] dba: 42039815=0x2817a07
col 0; len 20; (20): 39 53 34 32 39 34 30 37 34 32 31 33 30 34 53 37 30 35 32 39
col 1; TERM
row#1[8005] dba: 42048929=0x2819da1
col 0; len 19; (19): 39 53 34 32 39 34 30 37 34 32 31 33 30 34 55 37 30 34 34
col 1; TERM
----- end of branch block dump -----
End dump data blocks tsn: 9 file#: 10 minblk 1804 maxblk 1804
其中
kdxcolev 3表示索引层级号，我们转储的是根节点，层级号为3.
kdxcolok 表示该索引上是否正在发生修改块结构的事务，0表示没有。
kdxcoopc 表示内部操作代码。
kdxconco 表示索引条目中列的数量，这里是3，属于联合索引。
kdxcosdc 表示索引结构发生变化的数量，当你修改表里某个某个索引键值时，该值增加。
kdxconro 表示当前索引节点中索引条目的数量，但注意不包括kdxbrlmc指针。
kdxcofbo 表示当前索引节点中可用空间的起始点相对当前块的位移量。
kdxcofeo 表示当前索引节点中可用空间的最尾端相对当前块的位移量。
kdxcoavs 表示当前索引块中的可用空间总量，也就是用kdxcofeo减去kdxcofbo得到的。
kdxbrlmc 表示分支节点的地址，该分支节点存放了索引键值小于row#0（在转储文档后半
部分显示）所含有的最小值的所有节点信息；
kdxbrsno 表示最后一个被修改的索引条目号，这里看到是0，表示该索引是新建的索引；
kdxbrbksz 表示可用数据块的空间大小。实际从这里已经可以看到，即便是PCTFREE设置为0，
也不能用足8192字节。
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
每个索引条目都指向一个分支节点。其中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的索引块所链接的最小键值。
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 -----
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/35489/viewspace-687190/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/35489/viewspace-687190/
1466
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
