René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

July 20, 2005: On shrinking table sizes

Im my article dated April 21th 2005 (On table sizes), I pointed out that Oracle does not free storage space with the delete statement.
Usually, this is a good thing because the 'deleted' space will be used for future insert statements. Also update statements that increase the data size can use such 'deleted' space. Technically, this is because Oracle does not move the high water mark downwards.
However, when there is a mass delete operation that won't be followed by insert or update statements, it might be advisable to really free the deleted space.
In order to demonstrate this, I create a table and insert 10000 records: 
create table table_size_test (
  a char(100),
  b number
) storage (initial 65K  next 65K  pctincrease 0) 
tablespace ts_01;

begin
  for i in 1 .. 10000 loop
    insert into table_size_test values
      (dbms_random.string('X', 100),i);
  end loop;
end;
/

commit;
I also create an index on the table: 
create index ix_table_size_test on table_size_test(a)
  storage (initial 65K  next 65K  pctincrease 0)
  tablespace ts_02;
Let's see how much space the table and index use: 
select substr(segment_name,1,20) segment, bytes / 1024 "Size [KB]" from user_segments
where segment_name in ('TABLE_SIZE_TEST', 'IX_TABLE_SIZE_TEST');

SEGMENT               Size [KB]
-------------------- ----------
TABLE_SIZE_TEST            1280
IX_TABLE_SIZE_TEST         1280
Half of the records in the table are deleted: 
delete from table_size_test where mod(b,2)=0;

commit;
No space is freed: 
select substr(segment_name,1,20) segment, bytes / 1024 "Size [KB]" from user_segments
where segment_name in ('TABLE_SIZE_TEST', 'IX_TABLE_SIZE_TEST');

SEGMENT               Size [KB]
-------------------- ----------
TABLE_SIZE_TEST            1280
IX_TABLE_SIZE_TEST         1280
Using alter table .. move defragments (if you want that expression) and stores the table more efficiently: 
alter table table_size_test move;

select substr(segment_name,1,20) segment, bytes / 1024 "Size [KB]" from user_segments
where segment_name in ('TABLE_SIZE_TEST', 'IX_TABLE_SIZE_TEST');

SEGMENT               Size [KB]
-------------------- ----------
TABLE_SIZE_TEST             640
IX_TABLE_SIZE_TEST         1280
However, there are two problems. First, the size of the index is not reduced. But even worse: the index is invalidated!: 
select status from user_indexes 
where index_name = 'IX_TABLE_SIZE_TEST';

STATUS
--------
UNUSABLE
The index needs to be rebuilt: 
alter index ix_table_size_test rebuild;
This not only validates the index again, but also shrinks its size: 
select status, bytes/1024 from 
  user_indexes join user_segments on
    index_name = segment_name
where index_name = 'IX_TABLE_SIZE_TEST';

STATUS   BYTES/1024
-------- ----------
VALID           704
Thus, using alter table move, the allocated size on the harddisk for table and index had been roughly decreased by 50%.

More on Oracle

This is an on Oracle article. The most current articles of this series can be found here.