Storage Structure of unique Indexes

The storage of the unique index is different from that of the non-unique index. The storage of the non-unique index needs to store the ROWID as a column, and the unique index stores the ROWID in the DATA area.

The storage of the unique index is different from that of the non-unique index. The storage of the non-unique index needs to store the ROWID as a column, and the unique index stores the ROWID in the DATA area.

The storage of the unique index is different from that of the non-unique index. The storage of the non-unique index needs to store the ROWID as a column, and the unique index stores the ROWID in the DATA area, the following shows how to store the unique index through the experiment output.

1. Create tables and Indexes
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 Index Tree

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 100 ';

The content of the trace file is as follows:
----- 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. Take a page node as an example to view the storage status of the page Node
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

The trace Output content is as follows:
Leaf block dump
====================
Header address 461914212 = 0x1b884064
Kdxcolev0
Kdxcolevflags = ---
Kdxcolok 0
Kdxcoopc 0x80: opcode = 0: iot flags = --- is converted = Y
Kdxconco 1
Kdxcosdc 0
Kdxconro 1, 520
Kdxcofbo 1076 = 0x434
Kdxcofeo 1899 = 0x76b
Kdxcoavs 823
Kdxlespl 0
Kdxlende 0
Kdxlenxt 16779333 = 0x1000845
Kdxleprv 0 = 0x0
Kdxledsz 6
Kdxlebksz8036
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

The output result shows that the ROWID in the unique index is stored in the DATA area.

,