Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Can you give a IOT link to read more about this structure? –  Atilla Ozgur Dec 19 '11 at 22:43
    
Added link to Oracle's "Database Concepts" –  Jack Douglas Dec 20 '11 at 10:10
add comment

1 Answer 1

up vote 2 down vote accepted

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                   
*/
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.