Storage of Oracle indexes in Databases

Oracle indexes are organized and stored in the form of a Balance Tree: stores the value of the index column, a part of the rowid (file number, block number, row number)

The following is an example:

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, create index idx_test on test (id)

3. Get the object_id of the index:

Select * from dba_objects where object_name = 'idx _ Test'

4. dump the index to the trace file.

Alter session set events 'immediate trace name treedump level 100'

Result: There are two layers and Four Leaf nodes.

Branch: 0x1800014 25165844 (0: nrow: 4, level: 1)
Leaf: 0x1800017 25165847 (-1: nrow: 540 rrow: 540)
Leaf: 0x1800018 25165848 (0: nrow: 533 rrow: 533)
Leaf: 0x1800015 25165845 (1: nrow: 533 rrow: 533)
Leaf: 0x1800016 25165846 (2: nrow: 394 rrow: 394)
----- End tree dump
0x1800017: hexadecimal address, which is expressed in December 10. The following conversion shows that the addresses are the same.

Select to_number ('123', 'xxxxxxxxxxxxx') from dual = 1800017

Select to_char ('20140901', 'xxxxxxxxxxxxx') from dual = 25165847

Oracle provides the file number and block number corresponding to the address obtained by dbms_utility (the input parameter is the decimal value ).

Select dbms_utility.data_block_address_file (25165847) fno,
Dbms_utility.data_block_address_block (25165847) bkno from dual

What I get is the 23rd part of file 6,

View the extents allocation and check that the dump information matches the extents information.

Select file_id, block_id, blocks from dba_extents where segment_name = 'idx _ Test'

The obtained values 6, 17, 8, and block 23 are in the range of 17 + 8.

5. You can see 23 of the dump 6 file:

Row #0 [1116] flag: ------, lock: 0, len = 12
Col 0; len 2; (2): c1 02
Col 1; len 6; (6): 01 80 00 0c 00 00
Row #1 [1128] flag: ------, lock: 0, len = 12
Col 0; len 2; (2): c1 03
Col 1; len 6; (6): 01 80 00 0c 00 01
Row #2 [1140] flag: ---- S-, lock: 2, len = 12
Col 0; len 2; (2): c1 04
Col 1; len 6; (6): 01 80 00 0c 00 02
Row #3 [1152] flag: ---- S-, lock: 2, len = 12 ...........

The first three rows are used as an example,

Row #0 row number.

The first column (id in this example) of col 0, len 2 indicates that the length is 2, and (2) indicates that it occupies two bytes, c1 02 is the storage representation of the id value (here the value is in hexadecimal notation of 1.

Select dump () from dual you can see = Typ = 2 Len = 2: c1, 2 (0 omitted)

Col 1 is rowid, 01 80 00 0c 00 00 is a part of rowid, also hexadecimal.

Verify rowid: query the database from the test table to obtain the rowid information and compare it with the index data of dump.

Select rowid, id, dbms_rowid.rowid_relative_fno ('aaarpraagaaamaaa') fno,
Dbms_rowid.rowid_block_number ('aaarpraagaaamaaa') bkno,
Dbms_rowid.rowid_row_number ('aaarpraagaaamaaa') rno
From test where id = 1

The preceding SQL statement shows 12 blocks of 0 rows in file 6.

01 80 00 0c 00 00 must first be converted to a binary system:

00000001 10000000 00000000 00001100 00000000 00000000

The first 10 digits 00000001 10 after the strings are concatenated to indicate the file number, = 6

Then the next 22-digit 000000 00000000 00001100 represents the block number = 12

The last 16 bits indicate the row number = 0

Because the index stores the corresponding record rowid, The rowid changes after table move, and the index needs to be rebuilt.

Alter table test move; alter index idx_test rebuild online; (if not online, the update operation will be blocked)

Create a primary key and unique limitation on the table. If no index is available for the corresponding column, Oracle automatically creates an index with the same name as the corresponding constraint.