B tree索引学习总结
b tree索引
索引的结构：
1.创建一个测试表
create table it
(x number ,y varchar2(100));
2.插入一万行数据
begin
for i in 1..100000
loop
insert into it values(i,rpad(i,100,'*'));
end loop;
end;
/
3.创建索引在x列上
create index idx_it on it(x);
4.收集表信息
SQL> exec dbms_stats.gather_table_stats(user,'IT',cascade=>TRUE);
PL/SQL procedure successfully completed
5.将脏块写入文件
alter system flush buffer_cache;
6.查出表it的object_id
SQL> select object_id from dba_objects where object_name='IDX_IT';
OBJECT_ID
----------
86433
7.dump出整个索引结构
SQL> alter session set events 'immediate trace name treedump level 86433';
Session altered.
SQL> select * from v$diag_info where name='Default Trace File';
INST_ID NAME
---------- ----------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
1 Default Trace File
/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_5816.trc
8.vi部分dump文件
----- begin tree dump
branch: 0x1016403 16868355 (0: nrow: 222, level: 1)
leaf: 0x1016404 16868356 (-1: nrow: 485 rrow: 485)
leaf: 0x1016405 16868357 (0: nrow: 479 rrow: 479)
leaf: 0x1016406 16868358 (1: nrow: 479 rrow: 479)
leaf: 0x1016407 16868359 (2: nrow: 479 rrow: 479)
leaf: 0x1016408 16868360 (3: nrow: 479 rrow: 479)
leaf: 0x1016409 16868361 (4: nrow: 478 rrow: 478)
leaf: 0x101640a 16868362 (5: nrow: 479 rrow: 479)
leaf: 0x101640b 16868363 (6: nrow: 479 rrow: 479)
leaf: 0x101640c 16868364 (7: nrow: 479 rrow: 479)
leaf: 0x101640d 16868365 (8: nrow: 478 rrow: 478)
leaf: 0x101640e 16868366 (9: nrow: 479 rrow: 479)
leaf: 0x101640f 16868367 (10: nrow: 479 rrow: 479)
leaf: 0x1016411 16868369 (11: nrow: 479 rrow: 479)
leaf: 0x1016412 16868370 (12: nrow: 479 rrow: 479)
leaf: 0x1016413 16868371 (13: nrow: 478 rrow: 478)
leaf: 0x1016414 16868372 (14: nrow: 479 rrow: 479)
leaf: 0x1016415 16868373 (15: nrow: 479 rrow: 479)
leaf: 0x1016416 16868374 (16: nrow: 479 rrow: 479)
leaf: 0x1016417 16868375 (17: nrow: 479 rrow: 479)
leaf: 0x1016418 16868376 (18: nrow: 478 rrow: 478)
leaf: 0x1016419 16868377 (19: nrow: 475 rrow: 475)
leaf: 0x101641a 16868378 (20: nrow: 449 rrow: 449)
leaf: 0x101641b 16868379 (21: nrow: 449 rrow: 449)
leaf: 0x101641c 16868380 (22: nrow: 449 rrow: 449)
leaf: 0x101641d 16868381 (23: nrow: 449 rrow: 449)
leaf: 0x101641e 16868382 (24: nrow: 449 rrow: 449)
leaf: 0x101641f 16868383 (25: nrow: 449 rrow: 449)
leaf: 0x1016421 16868385 (26: nrow: 449 rrow: 449)
leaf: 0x1016422 16868386 (27: nrow: 449 rrow: 449)
leaf: 0x1016423 16868387 (28: nrow: 449 rrow: 449)
leaf: 0x1016424 16868388 (29: nrow: 449 rrow: 449)
leaf: 0x1016425 16868389 (30: nrow: 449 rrow: 449)
leaf: 0x1016426 16868390 (31: nrow: 449 rrow: 449)
leaf: 0x1016427 16868391 (32: nrow: 449 rrow: 449)
leaf: 0x1016428 16868392 (33: nrow: 449 rrow: 449)
leaf: 0x1016429 16868393 (34: nrow: 449 rrow: 449)
leaf: 0x101642a 16868394 (35: nrow: 449 rrow: 449)
每一行的第一列表示节点类型：branch表示分支节点（包括根节点），而leaf则表示叶子节点；
第二列表示十六进制表示的节点的地址；DBA
第三列表示十进制表示的节点的地址 DBA
第四列表示相对于前一个节点的位置，根节点从0开始计算，其他分支节点和叶子节点从-1开始计算
第五列的nrow表示当前节点中所含有的索引条目的数量。比如我们可以看到根节点中含有的nrow为9，表示根节点中含有9个索引条目，分别指向9个分支节点；
根节点及分支节点第六列中的level表示分支节点的层级，对于叶子节点来说level都是0；
叶子节点第六列中的rrow表示有效的索引条目（因为索引条目如果被删除，不会立即被清除出索引块中。
所以nrow减rrow的数量就表示已经被删除的索引条目数量）的数量，比如对于第一个leaf来说，其rrow为359，也就是说该叶子节点中存放了359个可用索引条目，分别指向表warecountd的359条记录。
9.
SQL> select dbms_utility.data_block_address_file(16868355) "file",dbms_utility.data_block_address_block(16868355) "block" from dual;
file block
---------- ----------
4 91139
注：data_block_address_file：This function gets the file number part of a data block address
使用查询视图进行验证：
SQL> select HEADER_FILE,HEADER_BLOCK from dba_segments where segment_name='IDX_IT';
HEADER_FILE HEADER_BLOCK
----------- ------------
4 91138
注：这个段头文件，所以需要+1,如此就是index的根节点
10.dump出file 4 block 91139
SQL> alter system dump datafile 4 block 91139;
System altered.
vi /app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_5816.trc
Branch block dump
=================
header address 47125592066636=0x2adc482cca4c
kdxcolev 1 --其中的kdxcolev表示索引层级号，这里由于我们转储的是根节点，所以其层级号为1
KDXCOLEV Flags = - - -
kdxcolok 0 --kdxcolok表示该索引上是否正在发生修改块结构的事务
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y --kdxcoopc表示内部操作代码
kdxconco 2 --kdxconco表示索引条目中列的数量(列 rowid,索引值)
kdxcosdc 0 --kdxcosdc表示索引结构发生变化的数量，当你修改表里的某个索引键值时，该值增加；
kdxconro 221 --kdxconro表示当前索引节点中索引条目的数量，但是注意，不包括kdxbrlmc指针
kdxcofbo 470=0x1d6 --kdxcofbo表示当前索引节点中可用空间的起始点相对当前块的位移量
kdxcofeo 5868=0x16ec --kdxcofeo表示当前索引节点中可用空间的最尾端的相对当前块的位移量
kdxcoavs 5398 --kdxcoavs表示当前索引块中的可用空间总量，等于 kdxcofeo-kdxcofbo得到的。
kdxbrlmc 16868356=0x1016404 --kdxbrlmc表示分支节点的地址，该分支节点存放了索引键值小于row#0（在转储文档后半部分显示）所含有的最小值的所有节点信息
kdxbrsno 0 --kdxbrsno表示最后一个被修改的索引条目号，这里看到是0，表示该索引是新建的索引；
kdxbrbksz 8056 --kdxbrbksz表示可用数据块的空间大小
kdxbr2urrc 0
row#0[8047] dba: 16868357=0x1016405
col 0; len 3; (3): c2 05 57 --col 0表示该分支节点所链接的最小键值
col 1; TERM --如果根节点下没有其他的分支节点，则col 1为TERM
row#1[8038] dba: 16868358=0x1016406
col 0; len 3; (3): c2 0a 42
col 1; TERM
row#2[8029] dba: 16868359=0x1016407
col 0; len 3; (3): c2 0f 2d
col 1; TERM
row#3[8020] dba: 16868360=0x1016408
col 0; len 3; (3): c2 14 18
col 1; TERM
row#4[8011] dba: 16868361=0x1016409
col 0; len 3; (3): c2 19 03
col 1; TERM
在获得最终物理块的过程中，我们不能同时读取多个块，因为我们在没有获得当前块的时候是不知道接下来应该访问哪个块的。因此，在索引上访问数据块时，会对应到db file sequential read等待事件，其根源在于我们是按照顺序从一个索引块跳到另一个索引块，从而找到最终的索引块的
那么对于全表扫描来说，则不存在访问下一个块之前需要先访问上一个块的情况。全表扫描时，oracle知道要访问所有的数据块，因此唯一的问题就是尽可能高效的访问这些数据块。因此，这时oracle可以采用同步的方式，分几批，同时获取多个数据块。这几批的数据块在物理上可能是分散在表里的，因此其对应到db file scattered read等待事件
2.B树索引的对于插入（INSERT）的管理
对于第一种情况来说，比较简单。当在一个充满了数据的表上创建索引（create index命令）时，oracle会先扫描表里的数据并对其进行排序，然后生成叶子节点。生成所有的叶子节点以后，根据叶子节点的数量生成若干层级的分支节点，最后生成根节点。这个过程是很清晰的
3个字节行头+1个字节列长+150个字节列本身+1个字节列长+6个字节ROWID
当第一个叶子节点充满以后，进行分裂时，先获得两个可用的索引块作为新的叶子节点，然后将当前该叶子节点里所有的索引条目拷贝到这两个新获得的叶子节点，
最后将原来的叶子节点改变为根节点.
analyze index idx_it validate structure;
不过要注意一点，就是该命令有一个坏处，就是在运行过程中，会锁定整个表，从而阻塞其他session对表进行插入、更新和删除等操作。
这是因为该命令的主要目的并不是用来填充index_stats视图的，其主要作用在于校验索引中的每个有效的索引条目都对应到表里的一行，
同时表里的每一行数据在索引中都存在一个对应的索引条目。为了完成该目的，所以在运行过程中要锁定整个表，
同时对于很大的表来说，运行该命令需要耗费非常多的时间
索引删除：总结
1） 当删除表里的一条记录时，其对应于索引里的索引条目并不会被物理的删除，只是做了一个删除标记。
2） 当一个新的索引条目进入一个索引叶子节点的时候，oracle会检查该叶子节点里是否存在被标记为删除的索引条目，如果存在，则会将所有具有删除标记的索引条目从该叶子节点里物理的删除。
3） 当一个新的索引条目进入索引时，oracle会将当前所有被清空的叶子节点（该叶子节点中所有的索引条目都被设置为删除标记）收回，从而再次成为可用索引块。
对于如何判断索引是否出现碎片，方法非常简单：直接运行ANALYZE INDEX … VALIDATE STRUCTURE
命令，然后检查index_stats视图的pct_used字段，如果该字段过低（低于50％），则说明存在碎片
索引的更新
索引的更新即 删除+插入的组合。
本文参考：http://blog.csdn.net/csd_xuming/article/details/7542584
学习到很多，谢谢！
