Sep 022013
 
delete 
from   previously_large_table;

insert /*+ append */ 
into   previously_large_table (
  cannot_reuse_existing_space,
  so_when_you
)
  select more_data, to_put_in
  from   here_space_consumed_grows;

When you delete, Oracle doesn't deallocate the space that was used by the table. 
Inserts using the append hint (aka direct path loads) allocates new space to the 
table, rather than re-using what's there. Misunderstanding of this can lead to 
tables consuming vast amounts of space, yet having (relatively) few rows in them - 
particularly if this is a frequent wipe-and-load staging table

For a quick and dirty script showing how this works (vs. convential inserts), keep reading. For more detailed discussions, take a look at this oracle-base article or this asktom question.

create table previously_large_table (
  cannot_reuse_existing_space varchar2(50), 
  so_when_you integer
);

create table here_space_consumed_grows (
  more_data varchar2(50), 
  to_put_in integer
);

insert into here_space_consumed_grows
  select dbms_random.string('x', 50), rownum
  from   dual
  connect by level <= 1000;

commit;

delete 
from   previously_large_table;

insert /*+ append */ 
into   previously_large_table (
  cannot_reuse_existing_space,
  so_when_you
)
  select more_data, to_put_in
  from   here_space_consumed_grows;

commit;

-- as we repeat this delete/insert cycle
-- the space allocated will just keep growing
select bytes from user_segments
where  segment_name = 'PREVIOUSLY_LARGE_TABLE';

delete 
from   previously_large_table;

insert /*+ append */ 
into   previously_large_table (
  cannot_reuse_existing_space,
  so_when_you
)
  select more_data, to_put_in
  from   here_space_consumed_grows;

commit;

select bytes from user_segments
where  segment_name = 'PREVIOUSLY_LARGE_TABLE';

delete 
from   previously_large_table;

insert /*+ append */ 
into   previously_large_table (
  cannot_reuse_existing_space,
  so_when_you
)
  select more_data, to_put_in
  from   here_space_consumed_grows;

commit;

select bytes from user_segments
where  segment_name = 'PREVIOUSLY_LARGE_TABLE';

drop table previously_large_table purge;
drop table here_space_consumed_grows purge;

-- repeat the experiment, but without 
-- the append hint and we'll see the 
-- space used remains constant
create table previously_large_table (
  cannot_reuse_existing_space varchar2(50), 
  so_when_you integer
);

create table here_space_consumed_grows (
  more_data varchar2(50), 
  to_put_in integer
);

insert into here_space_consumed_grows
  select dbms_random.string('x', 50), rownum
  from   dual
  connect by level <= 1000;

commit;

delete 
from   previously_large_table;

insert 
into   previously_large_table (
  cannot_reuse_existing_space,
  so_when_you
)
  select more_data, to_put_in
  from   here_space_consumed_grows;

commit;

select bytes from user_segments
where  segment_name = 'PREVIOUSLY_LARGE_TABLE';

delete 
from   previously_large_table;

insert 
into   previously_large_table (
  cannot_reuse_existing_space,
  so_when_you
)
  select more_data, to_put_in
  from   here_space_consumed_grows;

commit;

select bytes from user_segments
where  segment_name = 'PREVIOUSLY_LARGE_TABLE';

delete 
from   previously_large_table;

insert 
into   previously_large_table (
  cannot_reuse_existing_space,
  so_when_you
)
  select more_data, to_put_in
  from   here_space_consumed_grows;

commit;

select bytes from user_segments
where  segment_name = 'PREVIOUSLY_LARGE_TABLE';

drop table previously_large_table purge;
drop table here_space_consumed_grows purge;

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)