浅析oracle b-tree index搜索原理

索引与表一样，也属于段（segment）的一种。里面存放了用户的数据，跟表一样需要占用磁盘空间。只不过，在索引里的数据存放形式与表里的数据存放形式非常的不一样。在理解索引时，可以想象一本书，其中书的内容就相当于表里的数据，而书前面的目录就相当于该表的索引。同时，通常情况下，索引所占用的磁盘空间要比表要小的多，其主要作用是为了加快对数据的搜索速度。

但是，索引作为一种可选的数据结构，你可以选择为某个表里的创建索引，也可以不创建。这是因为一旦创建了索引，就意味着oracle对表进行DML（包括INSERT、UPDATE、DELETE）时，必须处理额外的工作量（也就是对索引结构的维护）以及存储方面的开销。所以创建索引时，需要考虑创建索引所带来的查询性能方面的提高，与引起的额外的开销相比，是否值得。

B树索引是一个典型的树结构，始终是平衡的，也就是说 从Root节点到 Leaf 节点的任何一个路径都是等距离的。其包含的组件主要是：

叶子节点（Leaf node）：包含条目直接指向表里的数据行。

分支节点（Branch node）：包含的条目指向索引里其他的分支节点或者是叶子节点。

根节点（Branch node）：一个B树索引只有一个根节点，它实际就是位于树的最顶端的分支节点。

对于分支节点块（包括根节点块）来说，其所包含的索引条目都是按照顺序排列的（可以指定reverse，倒序）。每个索引条目（也可以叫做每条记录）都具有两个字段。第一个字段表示当前该分支节点块下面所链接的索引块中所包含的最小键值（按B+tree最小键值复制原则）；第二个字段为四个字节，表示所链接的索引块的地址，该地址指向下面一个索引块。在一个分支节点块中所能容纳的记录行数由数据块大小以及索引键值的长度决定。

对于叶子节点块来说，其所包含的索引条目与分支节点一样，都是按照顺序排列的（缺省是升序排列，也可以在创建索引时指定为降序排列）。每个索引条目（也可以叫做每条记录）也具有两个字段。第一个字段表示索引的键值，对于单列索引来说是一个值；而对于多列索引来说则是多个值组合在一起的。第二个字段表示键值所对应的记录行的ROWID，该ROWID是记录行在表里的物理地址。在叶子节点中，每个索引条目都会在数据块中占一行空间。每一行用2到3个字节作为行头，行头用来存放标记以及锁定类型等信息。同时，在第一个表示索引的键值的字段中，每一个索引列都有1个字节表示数据长度，后面则是该列具体的值。

下面分别把分支节点的索引结构和叶子节点的索引信息dump出来

创建测试数据

[sql]

sys@ORCL> select * from v$version where
rownum=1;

BANNER

—————————————————————-

OracleDatabase 10g Enterprise Edition Release 10.2.0.1.0 – Prod

sys@ORCL> drop table tt purge;

drop table tt purge

*

ERROR at line 1:

ORA-00942: table or view does not exist

sys@ORCL> create table tt as select *
from dba_objects;

Table created.

sys@ORCL> select count(*) from tt;

COUNT(*)

———-

50356

sys@ORCL> insert into tt select * from
tt;

50356 rows created.

sys@ORCL> commit;

Commit complete.

sys@ORCL> select count(*) from tt;

COUNT(*)

———-

100712

sys@ORCL> create index btree_tt on
tt(object_name);

Index created.

查看索引的Blevel、height（blevel：节点的深度。root位于第0层，以此类推。height=blevel+1）

[sql]

sys@ORCL> select index_name,blevel from
dba_indexes where index_name=’BTREE_TT’;

INDEX_NAME BLEVEL

—————————— ———-

BTREE_TT 2

sys@ORCL> analyze index btree_tt
validate structure;

Index analyzed. sys@ORCL> select name,height from index_stats where
name=’BTREE_TT’;

NAME HEIGHT

—————————— ———-

BTREE_TT 3

获得btree_tt的对象号，进行索引结构的dump

[sql]

sys@ORCL> select object_id from
dba_objects where owner=’SYS’ and object_name=’BTREE_TT’;

OBJECT_ID

———-

52614

sys@ORCL> oradebug setmypid

Statement processed.

sys@ORCL> alter session set events
‘immediate trace name treedump level 52614’;

Session altered.

sys@ORCL> oradebug tracefile_name

/u01/app/oracle/admin/orcl/udump/orcl_ora_5234.trc

查看treedump trc文件

[sql]

—– begin tree dump

branch: 0x40efaa 4255658 (0: nrow: 2,
level: 2)

branch: 0x40f603 4257283 (-1: nrow: 247,
level: 1)

leaf:
0x40efab 4255659 (-1: nrow: 182 rrow: 182)

leaf:
0x40efac 4255660 (0: nrow: 182 rrow: 182)

leaf:
0x40efad 4255661 (1: nrow: 186 rrow: 186)

leaf:
0x40efae 4255662 (2: nrow: 189 rrow: 189)

leaf:
0x40efaf 4255663 (3: nrow: 186 rrow: 186)

leaf:
0x40efb0 4255664 (4: nrow: 190 rrow: 190)

leaf:
0x40efb1 4255665 (5: nrow: 185 rrow: 185)

leaf:
0x40efb2 4255666 (6: nrow: 179 rrow: 179)

leaf:
0x40efb3 4255667 (7: nrow: 187 rrow: 187)

leaf:
0x40efb4 4255668 (8: nrow: 181 rrow: 181)

……………………………………..

……………………………………..

branch: 0x40f6fb 4257531 (0: nrow: 248,
level: 1)

leaf:
0x40f602 4257282 (-1: nrow: 228 rrow: 228)

leaf:
0x40f604 4257284 (0: nrow: 226 rrow: 226)

leaf:
0x40f605 4257285 (1: nrow: 224 rrow: 224)

leaf:
0x40f606 4257286 (2: nrow: 223 rrow: 223)

leaf:
0x40f607 4257287 (3: nrow: 217 rrow: 217)

leaf:
0x40f608 4257288 (4: nrow: 253 rrow: 253)

leaf:
0x40f609 4257289 (5: nrow: 232 rrow: 232)

……………………………………..

……………………………………..

leaf:
0x40f6f8 4257528 (244: nrow: 191 rrow: 191)

leaf:
0x40f6f9 4257529 (245: nrow: 181 rrow: 181)

leaf:
0x40f6fa 4257530 (246: nrow: 99 rrow: 99)

—– end tree dump

解释trc文件

每一行第一列表示：节点类型，branch是分支节点（包括了根节点），而leaf则是叶子节点

第二列表示：节点地址，16进制

第三列表示：节点地址，10进制

第四列表示：相对于前一个节点的位置：根节点从0算起，其他分支节点和叶子节点从1开始算

第五列表示：（nrow）当前节点所含索引条目的数量（包括delete的条目）

第六列表示：（level）分支节点的层级，在oracle的索引中，层级号是倒过来的，也就是说假设某个索引有N层，则根节点的层级号为N，而根节点下一层的分支节点的层级号为N-1

第七列表示：（rrow）有效的索引条目的数量，因为索引条目如果被删除，不会立即被清除出索引块中。所以nrow减rrow的数量就表示已经被删除的索引条目数量

上面这种方式以树状形式转储整个索引。同时，我们可以转储一个索引节点来看看其中存放了些什么。

下面转储根节点的索引块内容。

从trc文件可知：根节点branch: 0x40efaa 4255658 (0: nrow: 2, level: 2)

[sql]

sys@ORCL> select
dbms_utility.data_block_address_file(4255658 ) fno，

2 dbms_utility.data_block_address_block(4255658
) bno

3 from dual;

FNO BNO

———- ———-

1 61354

sys@ORCL> alter system dump datafile 1
block 61354;

System altered.

sys@ORCL> oradebug tracefile_name

/u01/app/oracle/admin/orcl/udump/orcl_ora_5234.trc

查看root节点的trc内容

[sql]

header address 230057028=0xdb66444

kdxcolev 2

KDXCOLEV Flags = – – –

kdxcolok 0

kdxcoopc 0x80: opcode=0: iot flags=— is
converted=Y

kdxconco 2

kdxcosdc 0

kdxconro 1

kdxcofbo 30=0x1e

kdxcofeo 8026=0x1f5a

kdxcoavs 7996

kdxbrlmc 4257283=0x40f603

kdxbrsno 0

kdxbrbksz 8056

kdxbr2urrc 0

row#0[8026] dba: 4257531=0x40f6fb

col 0; len 18; (18): 41 4c 4c 5f 43 4f 4c 5f 50 52 49 56 53 5f 4d 41 44 45

col 1; len 6; (6): 00 40 ee c5 00 2c

—– end of branch block dump —–

kdxcolev 表示：索引层级号，我们这个例子中，根节点的level是2，叶子该是0

kdxcolok 表示：该索引上是否有DML活动事务

kdxconco 表示：索引条目中列的数量

kdxcosdc 表示：索引结构发生变化的数量，当你修改某个索引键值时，该值加1

kdxconro 表示：当前索引节点中索引条目的数量

kdxcofbo 表示：当前索引节点从第几个字节开始记录

kdxcofeo 表示：当前索引节点可用空间的最尾端在哪个字节

kdxcoavs 表示：当前索引节点可用空间总量。也就是kdxcofeo – kdxcofbo 的值

kdxbrlmc 表示：分支节点的位置

kdxbrsno 表示：最后一个被修改的索引条目号，这里为0，表明是新建索引

kdxbrbksz 表示：可用数据块大小，从这里我们可以知道，即便pctfree为0，对于8k数据块，我们也不能完全用完

[sql]

row#0[8026] dba: 4257531=0x40f6fb

col 0; len 18; (18): 41 4c 4c 5f 43 4f 4c 5f 50 52 49 56 53 5f 4d 41 44 45

col 1; len 6; (6): 00 40 ee c5 00 2c

这部分内容就是根节点里面记录的索引条目，总共1行（在B+树的定义里，如果按最小关键码复写原则，则树中每个非叶子节点中有m棵子树必有m-1个关键码）。每个索引条目都指向一个分支节点，其中，col 1表示所链接的分支节点的地址，如果根节点下没有其他的分支节点，则col 1为TERM；col 0表示该分支节点所链接的最小键值。注意一点，这里的col 0; len 18; (18):–列的行号，从0开始，紧接着的就是列的长度以及列的值，那么这个值称之为separator key,这个separator key 可以区分真实的索引值，所以从这里我们也知道 branch block不会存储完整的索引值，只要能区分就行。也就是说，Oracle在 Branch block中只记录 索引键值的前缀，而不是所有值，是因为这样可以节约空间，从而能够存储更多的索引条目。同时，我们也能理解了为什么 查询使用 like ‘%xxx’ 这种方法不会走Btree 索引，因为Branch block 存储的是前缀。

下面转储叶子节点块的内容

随便选一叶：leaf:
0x40f6fa 4257530 (246: nrow: 99 rrow: 99)

[sql]

sys@ORCL> select
dbms_utility.data_block_address_file(4257530) fno,

2 dbms_utility.data_block_address_block(4257530)
bno

3 from dual;

FNO BNO

———- ———-

1 63226

sys@ORCL> oradebug setmypid

Statement processed.

sys@ORCL> alter system dump datafile 1
block 63226；

sys@ORCL> oradebug tracefile_name

/u01/app/oracle/admin/orcl/udump/orcl_ora_6177.trc

叶子节点的部分内容摘入如下：

[sql]

Block header dump: 0x0040f6fa

Object id on Block? Y

seg/obj: 0xcd86 csc: 0x00.a3506 itc: 2 flg: – typ:
2 – INDEX

fsl: 0 fnx: 0x0 ver: 0x01

Itl Xid Uba Flag Lck Scn/Fsc

0x01
0x0000.000.00000000 0x00000000.0000.00 —- 0 fsc 0x0000.00000000

0x02
0xffff.000.00000000 0x00000000.0000.00 C— 0 scn 0x0000.000a3506

Leaf block dump

=免费云主机域名==============

header address 221234268=0xd2fc45c

kdxcolev 0

KDXCOLEV Flags = – – –

kdxcolok 0

kdxcoopc 0x80: opcode=0: iot flags=— is
converted=Y

kdxconco 2

kdxcosdc 0

kdxconro 99

kdxcofbo 234=0xea

kdxcofeo 4692=0x1254

kdxcoavs 4458

kdxlespl 0

kdxlende 0

kdxlenxt 0=0x0

kdxleprv 4257529=0x40f6f9

kdxledsz 0

kdxlebksz 8032

row#0[7992] flag: ——, lock: 0, len=40

col 0; len 30; (30):

73 75 6e 2f 74 6f 6f 6c 73 2f 74 72 65 65 2f 53 77 69 74 63 68 53
74 61 74

65 6d 65 6e 74

col 1; len 6; (6): 00 40 f3 25 00 0a

row#1[7953] flag: ——, lock: 0, len=39

col 0; len 29; (29):

73 75 6e 2f 74 6f 6f 6c 73 2f 74 72 65 65 2f 54 68 69 73 45 78 70
72 65 73

73 69 6f 6e

col 1; len 6; (6): 00 40 f0 74 00 31

row#2[7914] flag: ——, lock: 0, len=39

col 0; len 29; (29):

73 75 6e 2f 74 6f 6f 6c 73 2f 74 72 65 65 2f 54 68 69 73 45 78 70
72 65 73

73 69 6f 6e

col 1; len 6; (6): 00 40 f0 74 00 32

……………………….

……………………….

row#97[4727] flag: ——, lock: 0, len=35

col 0; len 25; (25):

79 43 62 43 72 53 75 62 53 61 6d 70 6c 69 6e 67 54 79 70 65 31 37
30 5f 54

col 1; len 6; (6): 00 40 f1 f1 00 0c

row#98[4692] flag: ——, lock: 0, len=35

col 0; len 25; (25):

79 43 62 43 72 53 75 62 53 61 6d 70 6c 69 6e 67 54 79 70 65 31 37
30 5f 54

col 1; len 6; (6): 00 40 f4 a2 00 10

—– end of leaf block dump —–

和分支节点不同的值解析如下：

kdxlespl 表示：当叶子节点被拆分时，未提交的事务数量

kdxlende 表示：被删除的索引条目数量

kdxlenxt 表示：当前叶子节点的下一个叶子节点的地址

kdxlprv 表示：当前叶子节点的上一个叶子节点的地址

kdxledsz 表示：被删除的空间

转储文件中接下来的部分就是索引条目部分。lock: 0 表示ITL中的锁信息 0表示没有被锁 ；len ：表示索引值长度 ；flag 表示 标记，如删除标记等。col 表示列号，从0开始 那么接下来就是索引的键值 以及 rowid中后三部分（相对文件号、块号、行号）即：col 0 是键值， col 1 是rowid。

也就是说，Leaf节点主要存储了完整的索引键值，以及相关索引键值的部分rowid(这个rowid去掉了data object number部分)，同时leaf 节点还存储了2个指针(DBA)，他们分别指向上一个leaf节点以及下一个leaf节点.这样叶子节点便是双向链表的结构。我们看到前面对B树索引的体系结构的描述，可以知道其为一个树状的立体结构。但对应到数据文件里的排列当然还是一个平面的形式，也就是像下面这样。因此，当oracle需要访问某个索引块的时候，势必会在这个结构上跳跃的移动。

/根/分支/分支/叶子/…/叶子/分支/叶子/叶子/…/叶子/分支/叶子/叶子/…/叶子/分支/…..

当oracle需要获得一个索引块时，首先从根节点开始，根据所要查找的键值，从而知道其所在的下一层的分支节点，然后访问下一层的分支节点，再次同样根据键值访问再下一层的分支节点，如此这般，最终访问到最底层的叶子节点。可以看出，其获得物理I/O块时，是一个接着一个，按照顺序，串行进行的。在获得最终物理块的过程中，我们不能同时读取多个块，因为我们在没有获得当前块的时候是不知道接下来应该访问哪个块的。因此，在索引上访问数据块时，会对应到db file sequential read等待事件，其根源在于我们是按照顺序从一个索引块跳到另一个索引块，从而找到最终的索引块的。

相关推荐: 更改online redo log file 提示 ORA-01511 ORA-01141

最近在迁移一个数据库，采用的方法是完全的物理拷贝至新的环境中，但是datafile的目录需要变更一下。前期的环境准备和物理拷贝十分顺利，但是在打开数据库至read only模式时却提示无法读取某个standby log，于是每个redo log都重新更改存储位…