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.
