Index internal oracle using simple test case

Index internal oracle using simple test case

First of all I will create a table with char(1000) so that in one block I can fit 6-7 entries .

Here are some relevant notes regarding Index split

For Leaf block

kdxlespl: bytes of uncommitted data at time of block split that have been cleaned out
kdxlende: number of deleted entries
kdxlenxt: pointer to the next leaf block in the index structure via corresponding rba
kdxleprv: pointer to the previous leaf block in the index structure via corresponding rba
Kdxledsz: deleted space
kdxlebksz: usable block space (by default less than branch due to the additional ITL entry)

For branch block

kdxbrlmc: block address if index value is less than the first (row#0) value
kdxbrsno: last index entry to be modified
kdxbrbksz: size of usable block space

Notes on 50-50 Block Split

An index block split is a relatively expensive operation:
1. Allocate new index block from index freelist
2. Redistribute block so the lower half (by volume) of index entries
remain in current block and move the other half into the new block
3. Insert the new index entry into appropriate leaf block
4. Update the previously full block such that its “next leaf block
pointer” (kdxlenxt) references the new block
5. Update the leaf block that was the right of the previously full block
such that its “previous leaf block pointer”(kdxleprv) also points to
the new block
6. Update the branch block that references the full block and add a
new entry to point to the new leaf block (effectively the lowest
value in the new leaf block)

50-50 Root Block Split

Root block is just a special case of a branch block:
1. Allocate two new blocks from the freelist
2. Redistributed the entries in the root block such that half the
entries are placed in one new block, the other half in the
other block
3. Update the root block such that it now references the two
new blocks
Richard Foote – Index Internals 73
Root block is always physically the same block
Root block split is the only time when the height of index increases
Therefore an index must always be balanced. Always !!
Suggestions that Oracle indexes become unbalanced are another
silly myth, made by those that don’t understand index block splits

90-10 Block Split

• If the new insert index entry is the maximum value, a 90-10 block split is performed
• Reduces wastage of space for index with monotonically increasing values
• Rather than leaving behind ½ empty blocks,full index blocks are generated
• I prefer to call them 99-1 block splits as 90-10 is misleading

1.Test Case preparation:-We will see how many block has been allocated.

SQL> create table test_ind(c1 char(1000));
Table created.
SQL> insert into test_ind values(‘A’);
1 row created.
SQL> insert into test_ind values(‘B’);
1 row created.
SQL>  insert into test_ind values(‘C’);
1 row created.
SQL> insert into test_ind values(‘D’);
1 row created.
SQL> insert into test_ind values(‘E’);
1 row created.
SQL> insert into test_ind values(‘F’);
1 row created.
SQL> commit;
Commit complete.
SQL> create index i_test_ind on test_ind(c1);
Index created.
SQL>  exec dbms_stats.gather_table_stats(‘C##TEST’,’TEST_IND’);
SQL> select EXTENT_ID,FILE_ID,BLOCK_ID,BLOCKS from dba_extents where SEGMENT_NAME=’I_TEST_IND’;
EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS
———- ———- ———- ———-
        0          6        608          8

SQL> select EXTENT_ID,FILE_ID,BLOCK_ID,BLOCKS from dba_extents where SEGMENT_NAME=’I_TEST_IND’;
EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS
———-             ———-    ———-          ———-
        0                  6                 608                   8
SQL> select object_id from dba_objects where object_name=’I_TEST_IND’;
OBJECT_ID
———-
    91946
SQL> alter session set events ‘immediate trace name treedump level 91946’;
Session altered.
SQL> alter system checkpoint;
System altered.
SQL> ALTER SYSTEM DUMP DATAFILE 6 block 611;
System altered.
SQL> ALTER SYSTEM DUMP DATAFILE 6 block 612;
System altered.
SQL> ALTER SYSTEM DUMP DATAFILE 6 block 613;
System altered.
SQL> ALTER SYSTEM DUMP DATAFILE 6 block 614;
System altered.
SQL> ALTER SYSTEM DUMP DATAFILE 6 block 615;

A.The treedump will show now the branch and leaf block entry:-

—– begin tree dump

leaf: 0x1800263 25166435 (0: row:6.6 avs:1918)

—– end tree dump

Now we can see the block_id start with 608, In ASSM set to auto: Add 3 to BLOCK_ID to find Root Block (can vary)

 

B.Dump of Leaf block 611 (Actually Branch block as only one index block allocated)

So root block is 611.We need to dump the content of block_id 611 for deeper analysis.

*** 2018-01-31 05:29:46.417
Start dump data blocks tsn: 4 file#:6 minblk 611 maxblk 611
Block dump from cache:
Dump of buffer cache at level 4 for pdb=1 tsn=4 rdba=25166435
7FCD89CE8810 00000000 2046E883 20202020 20202020  [……F         ]
7FCD89CE8820 20202020 20202020 20202020 20202020  [                ]
       Repeat 60 times
7FCD89CE8BF0 20202020 20202020 20202020 01062020  [              ..]
7FCD89CE8C00 005D0280 83000005 202045E8 20202020  [..]……E      ]
7FCD89CE8C10 20202020 20202020 20202020 20202020  [                ]
       Repeat 61 times
7FCD89CE8FF0 80010620 04005D02 E8830000 20202044  [ ….]……D   ]
7FCD89CE9000 20202020 20202020 20202020 20202020  [                ]
       Repeat 61 times
7FCD89CE93E0 20202020 02800106 0003005D 43E88300  [    ….]……C]
7FCD89CE93F0 20202020 20202020 20202020 20202020  [                ]
       Repeat 61 times
7FCD89CE97D0 20202020 06202020 5D028001 00000200  [       ….]….]
7FCD89CE97E0 2042E883 20202020 20202020 20202020  [..B             ]
7FCD89CE97F0 20202020 20202020 20202020 20202020  [                ]
       Repeat 60 times
7FCD89CE9BC0 20202020 20202020 01062020 005D0280  [          ….].]
7FCD89CE9BD0 83000001 202041E8 20202020 20202020  […..A          ]
7FCD89CE9BE0 20202020 20202020 20202020 20202020  [                ]
       Repeat 60 times
7FCD89CE9FB0 20202020 20202020 20202020 80010620  [             …]
7FCD89CE9FC0 00005D02 00000000 00000000 00000000  [.]…………..]
7FCD89CE9FD0 00000000 00000000 00000000 00000000  […………….]
       Repeat 1 times
7FCD89CE9FF0 00000000 00000000 00000000 B8A70601  […………….]
Object id on Block? Y
seg/obj: 0x1672a  csc: 0x00.32b8a5  itc: 2  flg: E  typ: 2 – INDEX
    brn: 0  bdba: 0x1800260 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   0xffff.000.00000000  0x00000000.0000.00  C—    0  scn 0x0000.0032b8a5
Leaf block dump
===============
header address 140520757035108=0x7fcd89ce8064
kdxcolev 0
KDXCOLEV Flags = – – –
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 6
kdxcofbo 48=0x30
kdxcofeo 1966=0x7ae
kdxcoavs 1918
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8032
row#0[7021] flag: ——-, lock: 0, len=1011
.
.
row#5[1966] flag: ——-, lock: 0, len=1011
—– end of leaf block Logical dump —–
—– end of leaf block dump —–
End dump data blocks tsn: 4 file#: 6 minblk 611 maxblk 611

 

2.Test Case preparation:-To test index block split as new entry can not be accommodated in the old leaf.

SQL> insert into test_ind values(‘G’);

1 row created.

SQL>  insert into test_ind values(‘J’);

1 row created.

SQL>  exec dbms_stats.gather_table_stats(‘C##TEST’,’TEST_IND’);

PL/SQL procedure successfully completed.

Please note LEAF_BLOCKS has been increased to 2

CLUSTERING_FACTOR is 2

SQL> select sn.name, ms.value
from v$statname sn, v$mystat ms
where sn.statistic#=ms.statistic#
and sn.name like ‘%leaf node %’ 2 3 4 ;

NAME VALUE
—————————————————————- ———-
leaf node splits 1
leaf node 90-10 splits 1

Please note that this is 90-10 split as new index entry added after max value.

A.The treedump will show now the branch and leaf block entry:-

—– begin tree dump
branch: 0x1800263 25166435 (0: nrow: 2, level: 1)
  leaf: 0x1800266 25166438 (-1: row:7.7 avs:905)
  leaf: 0x1800267 25166439 (0: row:1.1 avs:6983)
—– end tree dump

B.Dump of Branch block 611

Dump of buffer cache at level 4 for pdb=1 tsn=4 rdba=25166435
 
Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x000b.018.0000023d  0x01401354.0064.01  -BU-    1  fsc 0x0000.0032e067

C.Dump of Leaf block 614

      Repeat 55 times
7FF65F363420 41E88300 20202020 20202020 20202020  […A            ]
7FF65F363430 20202020 20202020 20202020 20202020  [                ]
       Repeat 60 times
7FF65F363800 20202020 20202020 06202020 5D028001  [           ….]]
7FF65F363810 00000000 2042E883 20202020 20202020  [……B         ]
7FF65F363820 20202020 20202020 20202020 20202020  [                ]
       Repeat 60 times
7FF65F363BF0 20202020 20202020 20202020 01062020  [              ..]
7FF65F363C00 005D0280 83000001 202043E8 20202020  [..]……C      ]
7FF65F363C10 20202020 20202020 20202020 20202020  [                ]
       Repeat 61 times
7FF65F363FF0 80010620 02005D02 E8830000 20202044  [ ….]……D   ]
7FF65F364000 20202020 20202020 20202020 20202020  [                ]
       Repeat 61 times
7FF65F3643E0 20202020 02800106 0003005D 45E88300  [    ….]……E]
7FF65F3643F0 20202020 20202020 20202020 20202020  [                ]
       Repeat 61 times
7FF65F3647D0 20202020 06202020 5D028001 00000400  [       ….]….]
7FF65F3647E0 2046E883 20202020 20202020 20202020  [..F             ]
7FF65F3647F0 20202020 20202020 20202020 20202020  [                ]
       Repeat 60 times
7FF65F364BC0 20202020 20202020 01062020 005D0280  [          ….].]
7FF65F364BD0 83000005 202047E8 20202020 20202020  […..G          ]
7FF65F364BE0 20202020 20202020 20202020 20202020  [                ]
       Repeat 60 times
7FF65F364FB0 20202020 20202020 20202020 80010620  [             …]
7FF65F364FC0 06005D02 00000000 00000000 00000000  [.]…………..]
7FF65F364FD0 00000000 00000000 00000000 00000000  […………….]
       Repeat 1 times
Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x000b.018.0000023d  0x01401356.0064.01  CB–    0  scn 0x0000.0032e067
0x02   0x0014.014.0000023d  0x01400467.0060.01  C—    0  scn 0x0000.0032e069

D.Dump of Leaf block 615

7FF65F364030 00000000 00000002 00001FE8 00000001  […………….]
7FF65F364040 00000000 00000002 00000003 00000000  […………….]
7FF65F364050 00000000 00000000 00000000 00000000  […………….]
       Repeat 183 times
7FF65F364BD0 83000000 20204AE8 20202020 20202020  […..J          ]
7FF65F364BE0 20202020 20202020 20202020 20202020  [                ]
Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x000b.018.0000023d  0x01401356.0064.02  CB–    0  scn 0x0000.0032e067
0x02   0x0014.014.0000023d  0x01400467.0060.03  C—    0  scn 0x0000.0032e069

3.Test Case preparation:-To test index block split as an update can not be accommodated in the old leaf.

SQL> update TEST_IND set c1=’H’ where c1=’G’;

1 row updated.

SQL> commit;

Commit complete.

SQL>  exec dbms_stats.gather_table_stats(‘C##TEST’,’TEST_IND’);

PL/SQL procedure successfully completed.

SQL> select sn.name, ms.value
from v$statname sn, v$mystat ms
where sn.statistic#=ms.statistic#
and sn.name like ‘%leaf node %’ 2 3 4 ;

NAME VALUE
—————————————————————- ———-
leaf node splits 2
leaf node 90-10 splits 1

Here is will happen 50-50 block split because Value ‘G’ is updated to ‘H’

A.Dump of Leaf block 615

7F698E4E47D0 00000000 00000000 00000000 00030000 […………….]
7F698E4E47E0 2047E883 20202020 20202020 20202020 [..G ]
7F698E4E47F0 20202020 20202020 20202020 20202020 [ ]
Repeat 60 times
7F698E4E4BC0 20202020 20202020 01062020 006B0280 [ ….k.]
7F698E4E4BD0 83000006 202048E8 20202020 20202020 […..H ]
7F698E4E4BE0 20202020 20202020 20202020 20202020 [ ]

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0003.017.00000b9d 0x01400d73.016f.01 CB– 0 scn 0x0000.003c23ae
0x02 0x0011.01d.000002b0 0x01400878.0065.18 C— 0 scn 0x0000.003c23b4
row#0[6010] flag: —DS–, lock: 0, len=1011
row#1[7021] flag: ——-, lock: 0, len=1011

B.Dump of Leaf block 616

Repeat 55 times
7F698E4E3420 41E88300 20202020 20202020 20202020 […A ]
7F698E4E3430 20202020 20202020 20202020 20202020 [ ]
Repeat 60 times
7F698E4E3800 20202020 20202020 06202020 6B028001 [ ….k]
7F698E4E3810 00000000 2041E883 20202020 20202020 [……A ]
7F698E4E3820 20202020 20202020 20202020 20202020 [ ]
Repeat 60 times
7F698E4E3BF0 20202020 20202020 20202020 01062020 [ ..]
7F698E4E3C00 006B0280 83000000 202042E8 20202020 [..k……B ]
7F698E4E3C10 20202020 20202020 20202020 20202020 [ ]
Repeat 61 times
7F698E4E3FF0 80010620 01006B02 E8830000 20202043 [ ….k……C ]
7F698E4E4000 20202020 20202020 20202020 20202020 [ ]
Repeat 61 times
7F698E4E43E0 20202020 02800106 0002006B 44E88300 [ ….k……D]
7F698E4E43F0 20202020 20202020 20202020 20202020 [ ]
Repeat 61 times
7F698E4E47D0 20202020 06202020 6B028001 00000300 [ ….k….]
7F698E4E47E0 2045E883 20202020 20202020 20202020 [..E ]
7F698E4E47F0 20202020 20202020 20202020 20202020 [ ]
Repeat 60 times
7F698E4E4BC0 20202020 20202020 01062020 006B0280 [ ….k.]
7F698E4E4BD0 83000004 202046E8 20202020 20202020 […..F ]
7F698E4E4BE0 20202020 20202020 20202020 20202020 [ ]

 

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0003.017.00000b9d 0x01400d72.016f.01 -BU- 1 fsc 0x0000.003c23ae
0x02 0x0011.01d.000002b0 0x01400878.0065.17 —- 0 fsc 0x0000.00000000
row#0[1966] flag: ——-, lock: 0, len=1011
row#1[2977] flag: ——-, lock: 0, len=1011
row#2[3988] flag: ——-, lock: 0, len=1011
row#3[4999] flag: ——-, lock: 0, len=1011
row#4[6010] flag: ——-, lock: 0, len=1011
row#5[7021] flag: ——-, lock: 0, len=1011

C.Dump of Leaf block 617

7F698E4E4BD0 83000000 20204AE8 20202020 20202020 […..J ]
7F698E4E4BE0 20202020 20202020 20202020 20202020 [ ]
Repeat 60 times
7F698E4E4FB0 20202020 20202020 20202020 80010620 [ …]
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0003.017.00000b9d 0x01400d73.016f.02 –U- 1 fsc 0x0000.003c23ae
0x02 0x0009.00b.00000b36 0x0140a425.014d.07 C— 0 scn 0x0000.003bdb96
row#0[7021] flag: ——-, lock: 0, len=1011
kdxleprv 25166460=0x180027c

4.Test Case preparation:-I will re-insert value ‘G’ again which was updated previously from ‘H’.

In that case we can see old index space will be re-used.There will not be any new addition of leaf block/value.

SQL> insert into TEST_IND values(‘G’);

1 row created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(‘C##TEST’,’TEST_IND’);

PL/SQL procedure successfully completed.

 

A.Dump of Leaf block 615

7FF4354A43E0 00000000 00000000 00000000 47E88302 [……………G]
7FF4354A43F0 20202020 20202020 20202020 20202020 [ ]
Repeat 61 times
7FF4354A47D0 20202020 06202020 6E028001 00030000 [ ….n….]
7FF4354A47E0 2047E883 20202020 20202020 20202020 [..G ]
7FF4354A47F0 20202020 20202020 20202020 20202020 [ ]
Repeat 60 times
7FF4354A4BC0 20202020 20202020 01062020 006B0280 [ ….k.]
7FF4354A4BD0 83000006 202048E8 20202020 20202020 […..H

 

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0003.017.00000b9d 0x01400d73.016f.01 CB– 0 scn 0x0000.003c23ae
0x02 0x0007.015.00000fb7 0x0142ef8c.011b.15 –U- 1 fsc 0x0000.003c4def

row#0[6010] flag: —DS–, lock: 0, len=1011

row#1[7021] flag: ——-, lock: 0, len=1011

row#2[7021] flag: ——-, lock: 0, len=1011

B.Dump of Leaf block 616

Repeat 55 times
7F0E2A993420 41E88300 20202020 20202020 20202020 […A ]
7F0E2A993430 20202020 20202020 20202020 20202020 [ ]
Repeat 60 times
7F0E2A993800 20202020 20202020 06202020 6B028001 [ ….k]
7F0E2A993810 00000000 2041E883 20202020 20202020 [……A ]
7F0E2A993820 20202020 20202020 20202020 20202020 [ ]
Repeat 60 times
7F0E2A993BF0 20202020 20202020 20202020 01062020 [ ..]
7F0E2A993C00 006B0280 83000000 202042E8 20202020 [..k……B ]
7F0E2A993C10 20202020 20202020 20202020 20202020 [ ]
Repeat 61 times
7F0E2A993FF0 80010620 01006B02 E8830000 20202043 [ ….k……C ]
7F0E2A994000 20202020 20202020 20202020 20202020 [ ]
Repeat 61 times
7F0E2A9943E0 20202020 02800106 0002006B 44E88300 [ ….k……D]
7F0E2A9943F0 20202020 20202020 20202020 20202020 [ ]
Repeat 61 times
7F0E2A9947D0 20202020 06202020 6B028001 00000300 [ ….k….]
7F0E2A9947E0 2045E883 20202020 20202020 20202020 [..E ]
7F0E2A9947F0 20202020 20202020 20202020 20202020 [ ]
Repeat 60 times
7F0E2A994BC0 20202020 20202020 01062020 006B0280 [ ….k.]
7F0E2A994BD0 83000004 202046E8 20202020 20202020 […..F ]
7F0E2A994BE0 20202020 20202020 20202020 20202020 [ ]
Repeat 60 times

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0003.017.00000b9d 0x01400d72.016f.01 -BU- 1 fsc 0x0000.003c23ae
0x02 0x0011.01d.000002b0 0x01400878.0065.17 —- 0 fsc 0x0000.00000000

C.Dump of Leaf block 617

Repeat 435 times
7F0E2A994BD0 83000000 20204AE8 20202020 20202020 […..J ]
7F0E2A994BE0 20202020 20202020 20202020 20202020 [ ]

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0003.017.00000b9d 0x01400d73.016f.02 –U- 1 fsc 0x0000.003c23ae
0x02 0x0009.00b.00000b36 0x0140a425.014d.07 C— 0 scn 0x0000.003bdb96

5.Test Case preparation:-Now we will test what will happen to CLUSTER_FACTOR and USED_SPACE.

CLUSTER_FACTOR will remain same.

SQL> analyze index I_TEST_IND validate structure;

Index analyzed.

SQL> analyze index I_TEST_IND validate structure;

Index analyzed.

About the Author

debasis maity

12+ years of rich experience on Database Administrations and on Infrastructure Solution Architect. AWS Certified Solution Architect and Senior Oracle DBA

Leave a Reply

Your email address will not be published. Required fields are marked *