Storage Structure of unique Indexes

Source: Internet
Author: User

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.