linux混合索引结构,唯一性索引的存储结构

唯一性索引的存储跟非唯一性索引的存储是不同的,非唯一性索引的存储需要将ROWID作为一列来存储,而唯一性索引将ROWID存储在DATA区,下面通过实验输出查看唯一性索引的存储情况.

1.创建表和索引

create table tb_unique_index_test

as

select * from dba_objects;

create unique index idx_tb_unique_index_test on tb_unique_index_test(object_id);

2.dump索引树

select object_id from dba_objects t

where t.owner='HXL'

and t.object_name ='IDX_TB_UNIQUE_INDEX_TEST'

OBJECT_ID

----------

70432

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

trace文件部分内容如下

----- begin tree dump

branch: 0x1000843 16779331 (0: nrow: 143, level: 1)

leaf: 0x1000844 16779332 (-1: nrow: 520 rrow: 520)

leaf: 0x1000845 16779333 (0: nrow: 513 rrow: 513)

leaf: 0x1000846 16779334 (1: nrow: 513 rrow: 513)

leaf: 0x1000847 16779335 (2: nrow: 513 rrow: 513)

leaf: 0x1000848 16779336 (3: nrow: 513 rrow: 513)

leaf: 0x1000849 16779337 (4: nrow: 513 rrow: 513)

leaf: 0x100084a 16779338 (5: nrow: 513 rrow: 513)

leaf: 0x100084b 16779339 (6: nrow: 513 rrow: 513)

leaf: 0x100084c 16779340 (7: nrow: 513 rrow: 513)

leaf: 0x100084d 16779341 (8: nrow: 513 rrow: 513)

leaf: 0x100084e 16779342 (9: nrow: 513 rrow: 513)

leaf: 0x100084f 16779343 (10: nrow: 513 rrow: 513)

leaf: 0x1000851 16779345 (11: nrow: 513 rrow: 513)

----- end tree dump

4.以某个页节点为例查看页节点的存储情况

SQL> select dbms_utility.data_block_address_file(16779332) file_no,

2    dbms_utility.data_block_address_block(16779332) block_no from dual;

FILE_NO   BLOCK_NO

---------- ----------

4       2116

sql> alter system dump datafile 4 block 2116

trace输出部分内容如下:

Leaf block dump

===============

header address 461914212=0x1b884064

kdxcolev 0

KDXCOLEV Flags = - - -

kdxcolok 0

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

kdxconco 1

kdxcosdc 0

kdxconro 520

kdxcofbo 1076=0x434

kdxcofeo 1899=0x76b

kdxcoavs 823

kdxlespl 0

kdxlende 0

kdxlenxt 16779333=0x1000845

kdxleprv 0=0x0

kdxledsz 6

kdxlebksz 8036

row#0[8025] flag: ------, lock: 0, len=11, data:(6):  01 00 01 8b 00 30

col 0; len 2; (2):  c1 03

row#1[8014] flag: ------, lock: 0, len=11, data:(6):  01 00 01 8b 00 05

col 0; len 2; (2):  c1 04

row#2[8003] flag: ------, lock: 0, len=11, data:(6):  01 00 01 8b 00 31

col 0; len 2; (2):  c1 05

row#3[7992] flag: ------, lock: 0, len=11, data:(6):  01 00 01 8b 00 1a

col 0; len 2; (2):  c1 06

row#4[7981] flag: ------, lock: 0, len=11, data:(6):  01 00 01 8b 00 15

col 0; len 2; (2):  c1 07

row#5[7970] flag: ------, lock: 0, len=11, data:(6):  01 00 01 8b 00 11

col 0; len 2; (2):  c1 08

row#6[7959] flag: ------, lock: 0, len=11, data:(6):  01 00 01 8b 00 24

col 0; len 2; (2):  c1 09

row#7[7948] flag: ------, lock: 0, len=11, data:(6):  01 00 01 8b 00 0d

col 0; len 2; (2):  c1 0a

row#8[7937] flag: ------, lock: 0, len=11, data:(6):  01 00 01 8b 00 25

col 0; len 2; (2):  c1 0b

row#9[7926] flag: ------, lock: 0, len=11, data:(6):  01 00 01 8b 00 38

col 0; len 2; (2):  c1 0c

row#10[7915] flag: ------, lock: 0, len=11, data:(6):  01 00 01 8b 00 28

col 0; len 2; (2):  c1 0d

row#11[7904] flag: ------, lock: 0, len=11, data:(6):  01 00 01 8b 00 0c

col 0; len 2; (2):  c1 0e

row#12[7893] flag: ------, lock: 0, len=11, data:(6):  01 00 01 8b 00 14

----- end of leaf block dump -----

End dump data blocks tsn: 4 file#: 4 minblk 2116 maxblk 2116

从以上输出结果可以看出唯一性索引的索引条目中的ROWID存储在DATA区.