唯一性索引的存储跟非唯一性索引的存储是不同的,非唯一性索引的存储需要将ROWID作为一列来存储,而唯一性索引将ROWID存储在DATA区
唯一性索引的存储跟非唯一性索引的存储是不同的,非唯一性索引的存储需要将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=0x1b8840
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 19=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[73] 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区.
