由浅至深讲解Oracle数据库 B-tree索引

假如聚簇因子过大，那么重建索引可能会有好处，聚簇因子应该接近块的数量，而非行的数量。

　　B-tree索引：

　　·索引会随着时间的增加而变的不平衡;

　　·删除的索引空间不会被重用;

　　·随着索引层数的增加，索引将会变得无效并需要重建;

　　·聚簇因子差，索引需要重建;

　　·为了提高性能，索引需要经常重建;

　　索引基础

　　·一个更新由一个删除和一个插入组成;

　　·页块由索引条目(row header(2/3B)|length(1B)|indexed data value(nB)|length(1B)|RowID(6B))和相应的rowid组成;

　　·每个页块包含两个指针分别前面的页块和后面页块;

 

Treedump 　　alter session  set  events ‘immediate trace name treedump level index_object_id’; 　　----- begin tree dump 　　branch: 0x424362 4342626 (0: nrow: 2, level: 1) 　　leaf: 0x424363 4342627 (-1: nrow: 540 rrow: 540) 　　leaf: 0x424364 4342628 (0: nrow: 461 rrow: 461) 　　----- end tree dump

　　以上dump包含的信息如下：

　　块类型：branch(分支块);leaf(页块);

　　块地址：0x424362 4342626;

　　nrow：索引条目的数量;

　　rrow：当前块中的索引条目数量;

　　level：分支块等级(页块隐示为0);

　　Block Dump
　　alter system  dump datafile X block X;
　　alter system dump datafile X block min X1 block max X2
　　Start dump data  blocks tsn: 0 file #: 1 minblk 148538 maxblk 148538
　　buffer tsn: 0 rdba: 0x0042443a (1/148538)
　　scn: 0x0000.00162a95 seq: 0x01 flg: 0x04 tail: 0x2a950601
　　frmt: 0x02 chkval: 0x8b5c type: 0x06=trans data
　　Block header dump: 0x0042443a
　　Object  id on Block? Y
　　seg/obj: 0xd1fe csc: 0x00.162a95 itc: 2 flg: O typ: 2 - INDEX
　　fsl: 0 fnx: 0x42443b ver: 0x01
　　Itl Xid Uba Flag  Lck Scn/Fsc
　　0x01 0x0005.02a.00000332 0x008005cb.020e.01 CB-- 0 scn 0x0000.00162a92
　　0x02 0x0008.011.00000346 0x008002e6.0163.03 C--- 0 scn 0x0000.00162a93

　　该dump包含的信息如下：

　　rdba：分支块的相对数据库块地址(文件号/块号);

　　scn：块最后改变的SCN号;

　　type：块类型;

　　seq：块改变的数量;

　　seg/obj: 16进制对象ID;

　　typ：段类型;

　　Itl：相关的事务槽(页块默认为2)，包括槽ID，事务ID，撤销块地址，标记，锁信息，和事务SCN;

　　通过rba确定数据文件号和块号：

 

select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(rba), 　　DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(rba) 　　from dual;

　　通用的索引块头

 

　　header address  153168988=0x9212c5c
　　kdxcolev 0
　　KDXCOLEV Flags = - - -
　　kdxcolok 1
　　kdxcoopc 0x89: opcode=9: iot flags=--- is converted=Y
　　kdxconco 2

　　kdxcosdc 2
　　kdxconro 254
　　kdxcofbo 544=0x220
　　kdxcofeo 4482=0x1182
　　kdxcoavs 3938
　　kdxcolev：索引级别(0代表页块);
　　kdxcolok：标示结构块事块是否发生;
　　kdxcoopc：内部操作码;
　　kdxconco：索引列数量，包括ROWID;
　　kdxcosdc：块中索引结构改变的数量;
　　kdxconro：索引条目的数量，不包括kdxbrlmc指针;
　　kdxcofbo：块中空闲空间的开始位置;
　　kdxcofeo：块中空闲空间的结束位置;
　　kdxcoavs：块中的可用空间数量(kdxcofbo-kdxcofeo);

　　分支头区域

 

kdxbrlmc 8388627=0x800013 　　kdxbrsno 92 　　kdxbrbksz 8060 　　kdxbrlmc：如果索引值小于第一个值(row #0)，则为该索引值所在的块地址; 　　kdxbrsno：最后更改的索引条目; 　　kdxbrbksz：可使用的块空间;

　　叶块头区域

 

kdxlespl 0 　　kdxlende 127 　　kdxlenxt 4342843=0x42443b 　　kdxleprv 4342845=0x42443d 　　kdxledsz 0 　　kdxlebksz 8036 　　kdxlespl：块拆分时 被清除的未提交数据的字节数; 　　kdxlende：被删除的条目数; 　　kdxlenxt：下一个页块的RBA; 　　kdxleprv：上一个页块的RBA; 　　kdxlebksz：可使用的块空间(默认小于分支的可用空间);

　　分支条目

 

　　row#0[7898] dba: 4342821=0x424425
　　col 0; len 3; (3): c2 61 03
　　col 1; TERM
　　row#1[7214] dba: 4342873=0x424459
　　col 0; len 4; (4): c3 04 02 17
　　col 1; TERM

　行号，[块中的起始位置] dba;

　　列号，列长度，列值;

　　brach中的每个entry有2个columns:

　　一个是child blocks中的最大值，另一个是指向的下一层block的address '

　　但是某些时候可能会有一些比较奇怪的结果：

 

row #0[7025] dba: 4342908=0x42447c 　　col 0; len 1024; (1024): 　　41 20 20 20 …20 　　col 1; len 4; (4): 00 42  44 73 　　----- end of branch block dump -----

　　叶条目

 

row#38[5014] flag : ----S-, lock: 2, len=14 　　col 0; len 4; (4): c3 04 61 55 　　col 1; len 6; (6): 00 42 43 db 00 a1 　　row#39[5028] flag: ---DS-, lock: 2, len=14

　　行号[在块中的开始位置] 各种标记(锁信息，删除信息);

　　索引列号，长度，值。其中6个字节的为ROWID号，将其转换为二进制，算法结果为：

　　前10 bit代表了file_id

　　中22 bit代表了block_id

　　后16 bit代表了row_id;

　　通过文件号和块号算出的结果为创建该索引的表的块。

　　奇怪的是,为什么索引中的rowid不能直接找到obj_id?

　　因为索引段对应的数据段在 一开始就知道，因为是先知道数据段才找到索引段，然后

　　根据索引段内容去搜索数据段内容，所以索引段中 rowid 不必包含 data_object_id 信息。

　　如果索引是建立在非分区表上，或者是分区表上的 LOCAL 索引，使用的是6 bytes的 Restricted ROWID。如果索引是建立在分区表上的 GLOBAL index，则使用 10bytes 的 Extended ROWID，这样可以区分索引指向哪个分区表。

　　更新/重用索引条目

　　当更新了索引条目后，DUMP如下：

 

kdxconco 2 　　kdxcosdc 0 　　kdxconro 2 　　kdxcofbo 40=0x28 　　kdxcofeo 8006=0x1f46 　　kdxcoavs 7966 　　kdxlespl 0 　　kdxlende 1 　　kdxlenxt 0=0x0 　　kdxleprv 0=0x0 　　kdxledsz 0 　　kdxlebksz 8036 　　row #0[8021] flag : ---D-, lock: 2 => deleted index entry 　　col 0; len 5; (5): 42  4f 57 49 45 　　col 1; len 6; (6): 00 80  05 0a 00 00 　　row#1[8006] flag: -----, lock: 2 　　col 0; len 5; (5): 5a 49 47 47 59 => new index entry 　　col 1; len 6; (6): 00 80 05 0a 00 00

　　更新后，将包含一个删除的条目，一个新的条目。在随后的插入中，如果新插入的索引条目能够放到被删除的索引条目的位置上，就会直接重用这个条目。根据索引值来决定。

　　所谓重用，是对row 的重用，而不是对row所在物理存储(或说物理位置)的重用。索引是按照indexed value对row进行排序的。有新的row被插入，首先按照value排序，将他放在合适的row list中，如果他的位置正好原来有个row被删掉了，则重用这个row在row list中的位置。至于物理存储上，则可能根据版本不同会有不同。在10.2中，我做的测试并没有向下开辟空间。

　　结论:

　　·到叶块中的任何插入都将移除所有被删除的条目;

　　·删除的空间在随后的写中被清除;

　　·删除的空间在延迟块清除中被清除;

　　·全空块被放在空闲列表，可以重用;

　　索引统计

 

·dba_indexes 　　·dbms_stats 　　·index_stats 　　-- analyze index index_name validate structure; 　　--分析资源，锁; 　　·v$segment_statistics 　　statistics_level = typical (or all)

　　注意事项：

　　blevel (dba_indexes) vs. height (index_stats)

　　blocks allocated，但未必使用;

　　lf_rows_len包含行负载(单列索引12个字节)

　　pct_used索引结构中当前使用的空间：(used_space/btree_space)*100

　　绝大多数索引统计包含删除的条目：

 

　　non-deleted rows = lf_rows – del_lf_rows
　　pct_used by non-deleted rows = ((used_space – del_lf_rows_len) / btree_space) * 100