WHAT HAPPENS TO INDEX ENTRY WHEN WE DELETE/UPDATE CORRESPONDING ROW FROM TABLE ??

WHAT HAPPENS TO INDEX ENTRY WHEN WE DELETE/UPDATE CORRESPONDING ROW FROM TABLE ??

create a table and index:

SQL> create table test_table (id number, name varchar2(20));

Table created.

SQL> insert into test_table values (1, 'JUNK1');

1 row created.

SQL> insert into test_table values (2, 'JUNK2');

1 row created.

SQL> commit;

Commit complete.

SQL> create index test_table_idx on test_table (id);

Index created.

SQL> select object_id from dba_objects where

     object_name='TEST_TABLE_IDX';

OBJECT_ID

----------

  73515

Now dump the Index structure:

SQL> alter session set events

     'immediate trace name treedump level 73515';

Session altered.

----- begin tree dump

leaf: 0x10007d3 16779219 (0: nrow: 2 rrow: 2)

----- end tree dump

nrow:  number of all index entries including deleted entries

rrows: number of current index entries

DBA: 16779219  (DATA BLOCK ADDRESS)

OBSERVATION: there is only one leafblock/root block which has two row entries.(no deleted entries as nrow = rrow)

Dump the leafblock:

We have only one leafblock which is also a root block, and

rootblock is allways next to header block.

SQL> select header_file, header_block from dba_segments where

     segment_name='TEST_TABLE_IDX';

HEADER_FILE HEADER_BLOCK

----------- ------------

          4         2002

So root block# = 2002+1

or we can find the block address using DBA (Which we got from treedump)

SQL> select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(16779219),

     DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(16779219) from dual;

File #            Block#

-----------------------------------------------                                                   4                 2003

Sql> alter system dump datafile 4 block 2003;

Session altered

Leaf block dump

===============

header address 3086706276=0xb7fb6664

kdxcolev 0

KDXCOLEV Flags = - - -

kdxcolok 0

kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y

kdxconco 2

kdxcosdc 0

kdxconro 2

kdxcofbo 40=0x28

kdxcofeo 8008=0x1f48

kdxcoavs 7968

kdxlespl 0

kdxlende 0

kdxlenxt 0=0x0

kdxleprv 0=0x0

kdxledsz 0

kdxlebksz 8032

row#0[8020] flag: ------, lock: 0, len=12

col 0; len 2; (2):  c1 02              à value of indexed column

col 1; len 6; (6):  01 00 07 cd 00 00   à rowid appended

row#1[8008] flag: ------, lock: 0, len=12

col 0; len 2; (2):  c1 03              à value of indexes column

col 1; len 6; (6):  01 00 07 cd 00 01   à rowid appended

----- end of leaf block dump -----

End dump data blocks tsn: 4 file#: 4 minblk 2003 maxblk 2003

Now Delete one row from table:

OBSERVATION: leaf block contains the two rows entries (one for each row in table).  

DELETE A ROW FROM TABLE:

Sql> delete from test_table where id=1;

1 row deleted

Sql> commit;

Commited

SQL>  alter session set events

      'immediate trace name treedump level  73515';

Session altered.

----- begin tree dump

leaf: 0x10007d3 16779219 (0: nrow: 2 rrow: 1)

----- end tree dump

Dumpfile indicates that we have only one current entry (rrow=1) 

And total rows including marked as delete = 2 (nrow=2)

 --Index has two rows entries even after we deleted a row from the table. i.e corresponding index is just marked as DELETE but not deleted physically.

Lets dump the leafblock:

SQL> alter system dump datafile 4 block 2003;

System altered

Leaf block dump

===============

header address 3086706276=0xb7fb6664

…………………………………………………………………………………………….

row#0[8020] flag: ---D--, lock: 2, len=12

col 0; len 2; (2):  c1 02 

col 1; len 6; (6):  01 00 07 cd 00 00     à deleted rowid

row#1[8008] flag: ------, lock: 0, len=12

col 0; len 2; (2):  c1 03

col 1; len 6; (6):  01 00 07 cd 00 01   à current existing rowid

----- end of leaf block dump -----

End dump data blocks tsn: 4 file#: 4 minblk 2003 maxblk 2003

Observations:

First row entry is still there but marked as DELETE with flag “D”

Now lets see what if inserted a new row into the table:

SQL> insert into test_table values (3, 'JUNK2');

1 row created.

SQL> commit;

Commit complete.

SQL> alter system dump datafile 4 block 2003;

System altered

Leaf block dump

===============

header address 3086706276=0xb7fb6664

…………………………………………………………………………………………….

row#0[8008] flag: ------, lock: 0, len=12

col 0; len 2; (2):  c1 03

col 1; len 6; (6):  01 00 07 cd 00 01   àold remaining rowid

row#1[7996] flag: ------, lock: 2, len=12

col 0; len 2; (2):  c1 04

col 1; len 6; (6):  01 00 07 cd 00 02      à New rowid

----- end of leaf block dump -----

End dump data blocks tsn: 4 file#: 4 minblk 2003 maxblk 2003

Observation:

As soon as a new rowentry added to the leaf block the row entry which was marked as ‘DELETE’ was actually deleted from the leafblock.

So row entries that are marked as DELETE will be physically deleted when new row is inserted into the leaf block.

This behaviour is confirmed also by dumping the tree structure:

----- begin tree dump

leaf: 0x10007d3 16779219 (0: nrow: 2 rrow: 2)  à no more deleted entry

----- end tree dump

What Happenes when the row updated ??

Now we know that if rows are deleted then corresponding index row entries are marked as DELETE initially and if any new row entries are inserted into the leaf blocks then it actually delets the  row entries that are marked as DELETTE previosly.

But what happens to the index row entries when corresponding rows are updated ??

SQL>  select * from test_table;

        ID                NAME

  ---------- --------------------

         2                        JUNK

         3                    JUNK2

SQL> alter system dump datafile 4 block 2003;

Leaf block dump

===============

header address 3086706276=0xb7fb6664

…………………………………………………………………………………………….

row#0[8008] flag: ------, lock: 0, len=12

col 0; len 2; (2):  c1 03

col 1; len 6; (6):  01 00 07 cd 00 01 

row#1[7996] flag: ------, lock: 2, len=12

col 0; len 2; (2):  c1 04

col 1; len 6; (6):  01 00 07 cd 00 02     

----- end of leaf block dump -----

End dump data blocks tsn: 4 file#: 4 minblk 2003 maxblk 2003

Now update the row:

SQL> update test_table set id=4 where name='JUNK2';

1  row updated.

SQL> commit;

Commit complete.

Leaf block dump

===============

header address 3086706276=0xb7fb6664

…………………………………………………………………………………………….

row#0[8008] flag: ------, lock: 0, len=12

col 0; len 2; (2):  c1 03

col 1; len 6; (6):  01 00 07 cd 00 01

row#1[7996] flag: ---D--, lock: 2, len=12   à entry marked as delete

col 0; len 2; (2):  c1 04

col 1; len 6; (6):  01 00 07 cd 00 02

row#2[7984] flag: ------, lock: 2, len=12   à New entry added

col 0; len 2; (2):  c1 05

col 1; len 6; (6):  01 00 07 cd 00 02

----- end of leaf block dump -----

End dump data blocks tsn: 4 file#: 4 minblk 2003 maxblk 2003

Observations: Even for updates oracle will follows the same procedure as of deletes.

i.e It will mark the old as DELETE and inserts new row for the updated value. If there is any further row insertes into the same leaf block then it will delete the rows entries that are marked as DELETED previously.

It can be confirmed by treedump also:

----- begin tree dump

leaf: 0x10007d3 16779219 (0: nrow: 3 rrow: 2)

----- end tree dump

Current rows = 2

Total rows including that marked as DELETE = 3

Once new rows are inserted into the same leaf blocks then oracle will delete the rowentries that are marked as DELETE previously.