Huaiyao's Notes

About Index Structure

Learn some about index structure.

Prepare data

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
SQL> create table rjin as select object_id,object_name from dba_objects where object_id<1000;
Table created.
SQL> create index rjin_idx on rjin(object_id);
Index created.
SQL> analyze index rjin_idx validate structure;
Index analyzed.
SQL> select BLEVEL,LEAF_BLOCKS,NUM_ROWS from dba_indexes where index_name='RJIN_IDX'
2 @pr
Pivoting output....
==============================
BLEVEL : 1
LEAF_BLOCKS : 3
NUM_ROWS : 997
PL/SQL procedure successfully completed.
SQL> select object_id from dba_objects where object_name='RJIN_IDX'
2 ;
OBJECT_ID
----------
75776

treedump

1
2
3
SQL> alter session set events 'immediate trace name treedump level 75776';
Session altered.
1
2
3
4
5
6
----- begin tree dump
branch: 0x100045b 16778331 (0: nrow: 3, level: 1)
leaf: 0x100045c 16778332 (-1: nrow: 485 rrow: 485)
leaf: 0x100045d 16778333 (0: nrow: 479 rrow: 479)
leaf: 0x100045e 16778334 (1: nrow: 33 rrow: 33)
----- end tree dump

One root block and three leaf blocks.
last leaf block has 33 index entries.

delete last row

1
2
3
4
5
6
7
8
9
10
11
SQL> delete from rjin where object_id=999;
1 row deleted.
SQL> commit;
Commit complete.
SQL> alter session set events 'immediate trace name treedump level 75776';
Session altered.
1
2
3
4
5
6
----- begin tree dump
branch: 0x100045b 16778331 (0: nrow: 3, level: 1)
leaf: 0x100045c 16778332 (-1: nrow: 485 rrow: 485)
leaf: 0x100045d 16778333 (0: nrow: 479 rrow: 479)
leaf: 0x100045e 16778334 (1: nrow: 33 rrow: 32)
----- end tree dump

We see current size is 32.

dump the index block

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SQL>
select
dbms_utility.data_block_address_file(16778334) file#,
dbms_utility.data_block_address_block(16778334) block#
from
dual;
FILE# BLOCK#
---------- ----------
4 1118
SQL> alter system flush buffer_cache;
System altered.
SQL> alter system dump datafile 4 block 1118;
System altered.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
Block header dump: 0x0100045e
Object id on Block? Y
seg/obj: 0x12800 csc: 0x00.1038bf itc: 2 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x1000458 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x02 0x000a.011.00000393 0x00c004a9.00fd.1f --U- 1 fsc 0x000f.0010397b
Leaf block dump
===============
header address 18446744071445703268=0xffffffff79106a64
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 33
kdxcofbo 102=0x66
kdxcofeo 7603=0x1db3
kdxcoavs 7501
kdxlespl 0
kdxlende 1
kdxlenxt 0=0x0
kdxleprv 16778333=0x100045d
kdxledsz 0
kdxlebksz 8032
row#0[8019] flag: ------, lock: 0, len=13
col 0; len 3; (3): c2 0a 44
col 1; len 6; (6): 01 00 04 56 00 63
row#1[8006] flag: ------, lock: 0, len=13
col 0; len 3; (3): c2 0a 45
col 1; len 6; (6): 01 00 04 56 00 64
row#2[7993] flag: ------, lock: 0, len=13
col 0; len 3; (3): c2 0a 46
col 1; len 6; (6): 01 00 04 56 00 65
...
...
...
row#30[7629] flag: ------, lock: 0, len=13
col 0; len 3; (3): c2 0a 62
col 1; len 6; (6): 01 00 04 56 00 81
row#31[7616] flag: ------, lock: 0, len=13
col 0; len 3; (3): c2 0a 63
col 1; len 6; (6): 01 00 04 56 00 82
row#32[7603] flag: ---D--, lock: 2, len=13
col 0; len 3; (3): c2 0a 64
col 1; len 6; (6): 01 00 04 56 00 83
----- end of leaf block dump -----

1

1
2
3
4
5
6
7
8
kdxconro 33
kdxcofbo 102=0x66
kdxcofeo 7603=0x1db3
kdxcoavs 7501
kdxlespl 0
kdxlende 1
kdxlenxt 0=0x0
kdxleprv 16778333=0x100045d

kdxconro 33 –> currently we have 33 entries.
kdxlenxt 0=0x0 –> no pointing to next leaf block, this is the last leaf block.
kdxleprv 16778333=0x100045d –> address for previous leaf block, see above treedump.

2

1
2
3
row#32[7603] flag: ---D--, lock: 2, len=13
col 0; len 3; (3): c2 0a 64
col 1; len 6; (6): 01 00 04 56 00 83

flag: —D– –> this entry was deleted.

3

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SQL> select max(object_id) from rjin;
MAX(OBJECT_ID)
--------------
998
SQL> select object_id,dump(object_id),rawtohex(object_id) from rjin where object_id=998;
OBJECT_ID
----------
DUMP(OBJECT_ID)
--------------------------------------------------------------------------------
RAWTOHEX(OBJECT_ID)
--------------------------------------------
998
Typ=2 Len=3: 194,10,99
C20A63

decimal to hex:
194 -> c2
10 -> 0a
99 -> 63

Match with (c2 0a 63):

1
2
3
row#31[7616] flag: ------, lock: 0, len=13
col 0; len 3; (3): c2 0a 63
col 1; len 6; (6): 01 00 04 56 00 82

4

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SQL> insert into rjin values(1000,'RJIN');
1 row created.
SQL> commit;
Commit complete.
SQL> alter system flush buffer_cache;
System altered.
SQL> alter system dump datafile 4 block 1118;
System altered.
1
2
3
4
5
6
7
8
9
10
row#30[7629] flag: ------, lock: 0, len=13
col 0; len 3; (3): c2 0a 62
col 1; len 6; (6): 01 00 04 56 00 81
row#31[7616] flag: ------, lock: 0, len=13
col 0; len 3; (3): c2 0a 63
col 1; len 6; (6): 01 00 04 56 00 82
row#32[7591] flag: ------, lock: 2, len=12
col 0; len 2; (2): c2 0b
col 1; len 6; (6): 01 00 04 56 00 84
----- end of leaf block dump -----

See new index entry.