探索组合索引的数据存储

Oracle的索引是以平衡树的方式组织存储的:保存的是索引列的值,以及该行的rowid的一部分(文件号,块号,行号)
下面我们通过例子来了解一下:

1,create table test(id int,name varchar2(20))
insert into test values(1,'A');
insert into test values(2,'B');

begin
for i in 3..2000 loop
insert into test values(i,'t'||i);
end loop;
end;

2,创建组合索引:

SQL> create index test_idx2 on test(id ,name);

Index created.

3,得到这个index的object_id:

SQL> select object_id  from dba_objects where object_name='TEST_IDX2';

 OBJECT_ID
----------
     74564

4,将索引dump到trace文件中

alter session set events 'immediate trace name treedump level 74564';

看到结果:有两层,6个叶子节点

----- begin tree dump
branch: 0x1000ca3 16780451 (0: nrow: 6, level: 1)
   leaf: 0x1000ca4 16780452 (-1: nrow: 369 rrow: 369)
   leaf: 0x1000ca5 16780453 (0: nrow: 359 rrow: 359)
   leaf: 0x1000ca6 1