One of the internal mechanisms of IOT.

When was the IOT’s PK column(s) updated, what will happen to the secondary index?

In the oracle world, IOT is not frequently used. Actually, I don’t know the reason. Because IOT can provide good peformance in many cases. Of course, IOT has some restrictions. For example, it can’t use direct load and so on. Anyway, this time we reveal one of the mechanisms of IOT.

As you already know, IOT can have a seconary index. And the leaf blocks of the seconary index include index key column, PK column(s) and physical guess (= Data Block Address). So, to find the proper data block, oracle initially uses a physical guess. If the physical guess isn’t correct, use the PK column(s) in secondary index.

If so, when was the IOT’s PK column(s) updated, what will happen to the secondary index? That is a very simple question. Maybe the contents of the secondary index will be changed. Let’s prove that.

1) Create a table, data loading and create a index.

create table iot (
 c1 number,
 c2 char(1),
 c3 number,
 dummy char(2000),
constraint pk_iot1
primary key (c1,c2))
organization index
pctfree 40;
 
insert into iot
select mod(level,5), case when level<=5 then 'A' else 'B' end , mod(level,5), 'dummy'
from   dual
connect by level<=10;
 
commit; 

create index iot_idx1 on iot(c3); 

2) Find object_id of iot_idx1 for index tree dump and execute dump.

select object_id from user_objects where object_name='IOT_IDX1';
 
 OBJECT_ID
----------
    150905
 
alter session set events 'immediate trace name treedump level 150905'; 
 
----- begin tree dump
leaf: 0x101405b 16859227 (0: nrow: 10 rrow: 10)
----- end tree dump

3) Get the file_id and block_number from DBA using dbms_utility.data_block_address_file(block)

-- input parameter must use decimal number. 
-- 16859227 is decimal number of 101405b.
 
select dbms_utility.data_block_address_file(16859227)  file_id,
       dbms_utility.data_block_address_block(16859227) block_id
from dual;
  
   FILE_ID   BLOCK_ID
---------- ----------
         4      82011 

4) Index leaf block dump and analyze it.

alter system dump datafile 4 block 82011;

Leaf block dump
===============
header address 18446744071449897572=0xffffffff79506a64
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x90: opcode=0: iot flags=I-- is converted=Y
kdxconco 3 --> The count of index columns. (1 for iot_idx1 and 2 for PK)
...
row#0[8016] flag: K-----, lock: 0, len=16
col 0; len 1; (1): 80 --> number 0
col 1; len 1; (1): 80 --> number 0 (leading edge of PK)
col 2; len 1; (1): 41 --> 'A'
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4] 01 01 40 44
row#1[8000] flag: K-----, lock: 0, len=16
col 0; len 1; (1): 80 --> number 0
col 1; len 1; (1): 80 --> number 0
col 2; len 1; (1): 42 --> 'B'
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4] 01 01 40 44
row#2[7982] flag: K-----, lock: 0, len=18
col 0; len 2; (2): c1 02
col 1; len 2; (2): c1 02
col 2; len 1; (1): 41
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4] 01 01 40 46
row#3[7964] flag: K-----, lock: 0, len=18
col 0; len 2; (2): c1 02
col 1; len 2; (2): c1 02
col 2; len 1; (1): 42
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4] 01 01 40 46
row#4[7946] flag: K-----, lock: 0, len=18
col 0; len 2; (2): c1 03
col 1; len 2; (2): c1 03
col 2; len 1; (1): 41
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4] 01 01 40 47
row#5[7928] flag: K-----, lock: 0, len=18
col 0; len 2; (2): c1 03
col 1; len 2; (2): c1 03
col 2; len 1; (1): 42
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4] 01 01 40 47
row#6[7910] flag: K-----, lock: 0, len=18
col 0; len 2; (2): c1 04
col 1; len 2; (2): c1 04
col 2; len 1; (1): 41
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4] 01 01 40 47
row#7[7892] flag: K-----, lock: 0, len=18
col 0; len 2; (2): c1 04
col 1; len 2; (2): c1 04
col 2; len 1; (1): 42
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4] 01 01 40 50
row#8[7874] flag: K-----, lock: 0, len=18
col 0; len 2; (2): c1 05
col 1; len 2; (2): c1 05
col 2; len 1; (1): 41
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4] 01 01 40 45
row#9[7856] flag: K-----, lock: 0, len=18
col 0; len 2; (2): c1 05
col 1; len 2; (2): c1 05
col 2; len 1; (1): 42
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4] 01 01 40 45
----- end of leaf block dump -----
End dump data blocks tsn: 4 file#: 4 minblk 82011 maxblk 82011

As you can see, in the leaf blocks of iot_idx1 index include PK columns. And then, update PK columns and dump the leaf block.

5) Update PK columns, and then index leaf block dump and analyze it.

update iot set c1=4 ,c2='C' where c1=1 and c2='A'; 
commit; 
 
alter system checkpoint; 
alter system flush buffer_cache; 
alter system dump datafile 4 block 82011; 

row#0[8016] flag: K-----, lock: 0, len=16
col 0; len 1; (1): 80
col 1; len 1; (1): 80
col 2; len 1; (1): 41
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4] 01 01 40 44
row#1[8000] flag: K-----, lock: 0, len=16
col 0; len 1; (1): 80
col 1; len 1; (1): 80
col 2; len 1; (1): 42
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4] 01 01 40 44
row#2[7982] flag: K--D--, lock: 2, len=18 --> This row was deleted.
col 0; len 2; (2): c1 02
col 1; len 2; (2): c1 02
col 2; len 1; (1): 41
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4] 01 01 40 46
row#3[7964] flag: K-----, lock: 0, len=18
col 0; len 2; (2): c1 02
col 1; len 2; (2): c1 02
col 2; len 1; (1): 42
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4] 01 01 40 46
row#4[7838] flag: K-----, lock: 2, len=18 --> This row was inserted.
col 0; len 2; (2): c1 02
col 1; len 2; (2): c1 05 --> number 4
col 2; len 1; (1): 43 --> 'C'
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4] 01 01 40 45 --> Maybe, Block Address
row#5[7946] flag: K-----, lock: 0, len=18
col 0; len 2; (2): c1 03
col 1; len 2; (2): c1 03
col 2; len 1; (1): 41
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4] 01 01 40 47
row#6[7928] flag: K-----, lock: 0, len=18
col 0; len 2; (2): c1 03
col 1; len 2; (2): c1 03
col 2; len 1; (1): 42
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4] 01 01 40 47
row#7[7910] flag: K-----, lock: 0, len=18
col 0; len 2; (2): c1 04
col 1; len 2; (2): c1 04
col 2; len 1; (1): 41
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4] 01 01 40 47
row#8[7892] flag: K-----, lock: 0, len=18
col 0; len 2; (2): c1 04
col 1; len 2; (2): c1 04
col 2; len 1; (1): 42
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4] 01 01 40 50
row#9[7874] flag: K-----, lock: 0, len=18
col 0; len 2; (2): c1 05
col 1; len 2; (2): c1 05
col 2; len 1; (1): 41
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4] 01 01 40 45
row#10[7856] flag: K-----, lock: 0, len=18
col 0; len 2; (2): c1 05
col 1; len 2; (2): c1 05
col 2; len 1; (1): 42
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4] 01 01 40 45
----- end of leaf block dump -----
End dump data blocks tsn: 4 file#: 4 minblk 82011 maxblk 82011

From the dump result, we can know that the column(s) of PK in the secondary index was updated(deleted and inserted), whenever PK column(s) are updated.