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 and a part of the rowid of the row (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:

SQL> select object_id from dba_objects where object_name = 'idx _ test ';
Object_id
----------
72300

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 address here is the address of the index data.

The following conversion shows that the above two data can be converted to each other.

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.

SQL> select file_id, block_id, blocks from dba_extents where segment_name = 'idx _ test ';

File_id block_id Blocks
------------------------------
6 17 8

The values 6, 17, 8 are obtained. Here, 6 is the file number, 17 is the block number starting from the interval, and block 23 is in the range 17 + 8.

5. Dump the index data. Dump 23 of file 6 and alter system dump datafile 6 block 23

You can see the content of the index 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 ., first, convert it to a binary system, and then calculate the file number, block number, and row number based on the meaning of each digit.

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

Verify that the rowid saved by the index is the same as the rowid information obtained from the test table.

SQL> select rowid, ID, dbms_rowid.rowid_relative_fno ('aaarpraagaaamaaa') fno,
2 dbms_rowid.rowid_block_number ('aaarpraagaaaaamaaa') bkno,
3 dbms_rowid.rowid_row_number ('aaarpraagaaaaamaaa') rno
4 From test where id = 1;

Rowid ID fno bkno rno
------------------------------------------------------------------------------------
Aaanp8aaeaaaagdaaa 1 6 12 0

The preceding SQL statement contains 12 0 rows in file 6, proving that the index contains a part of rowid.

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.

When creating a composite index, it is best to put the columns with multiple duplicate values in front of the index, so that the index can be used when querying the columns in order.