Do IOTs generate significantly more undo per block for deletes?

Question

IOTs are an index-like structure. When deleting large contiguous chunks of data, how much undo is generated as compared to a similar contiguous delete from a heap?

Answer 1

No, this type of delete generates roughly the same quantity of undo regardless of whether it is an IOT or heap.

In contrast, inserting into the IOT generates far more undo.

heap:

drop table foo;
create table foo( id integer not null, 
                  val char(100) default 'A' not null);
--
insert into foo(id) select level from dual connect by level<100000;
--
select used_ublk
from v$transaction join v$session on(addr=taddr)
where sid=sys_context('USERENV','SID');
/*
USED_UBLK              
---------------------- 
149                    
*/
--
analyze table foo compute statistics;
--
select blocks from all_tab_statistics where table_name='FOO';
/*
BLOCKS                 
---------------------- 
3274                   
*/
--
delete from foo;
--
select used_ublk
from v$transaction join v$session on(addr=taddr)
where sid=sys_context('USERENV','SID');
/*
USED_UBLK              
---------------------- 
5264                   
*/

IOT:

drop table foo;
create table foo( id integer not null primary key, 
                  val char(100) default 'A' not null) organization index;
--
insert into foo(id) select level from dual connect by level<100000;
--
select used_ublk
from v$transaction join v$session on(addr=taddr)
where sid=sys_context('USERENV','SID');
/*
USED_UBLK              
---------------------- 
2030                   
*/
--
analyze table foo compute statistics;
select leaf_blocks from all_ind_statistics where table_name='FOO';
/*
LEAF_BLOCKS            
---------------------- 
2942                   
*/
--
delete from foo;
select used_ublk
from v$transaction join v$session on(addr=taddr)
where sid=sys_context('USERENV','SID');
/*
USED_UBLK              
---------------------- 
4741                   
*/