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.

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.

 

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:-

B.Dump of Branch block 611

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.

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.