deleting from table causes fragmentation

One of our application groups periodically deletes data, which are older than 10 days, from some tables. This periodic action splits the table blocks to a wide range of datafile blocks. This causes performance problems on daily data loading and daytime reporting. As the first action plan gather statistics for trusted analysis.

begin
dbms_stats.gather_table_stats(
    ownname=>'PQ',
    tabname=>'GPRS_CELL_GPRS',
    estimate_percent=>33,
    degree=>2,
    cascade=>true);
end;

You can see the space fragmentation from the following. Currently the tables consumes nearly 3Gb 's of datafile usage. But if you calculate the average row length with the number of table rows, datafile usage should be 328Mb.

select ceil((blocks*8)/1024) as table_mb from dba_tables 
    where table_name='GPRS_CELLGPRS' and owner='PQ'
/*
TABLE_MB
2926
*/
select ceil((avg_row_len*num_rows)/1024/1024) as table_mb from dba_tables 
    where table_name='GPRS_CELLGPRS' and owner='PQ'
/*
TABLE_MB
328
*/ 

After this finding, following procedure is set to run periodically to delete 10 days older data from the table. After the delete operation table is moved for defragmentation. Table indexes should also be rebuilded. After the move and rebuild operation statistics should also be gathered. Keep in mind that table move and index rebuild operations blocks user access.

create or replace procedure avea_sysmon.P_TABLE_MAINTENANCE
  (g_table_name varchar2, g_owner varchar2) AUTHID CURRENT_USER as 
  
  cursor c1 is
    select * from sys.dba_indexes where table_owner=g_owner and table_name=g_table_name;

  r1 c1%rowtype;  
begin
    
    execute immediate 'delete from PQ.GPRS_TRAFGPRS3 where ' || 
                      'to_number(substr(CDATE,1,6)) < to_char(sysdate-15,''YYMMDD'')';
    commit; 
    execute immediate 'alter table ' || g_owner || '.' || g_table_name || ' move';
    
    open c1;    
    loop
      fetch c1 into r1;
      exit when c1%NOTFOUND;  
      execute immediate 'alter index ' || r1.table_owner || '.' || 
                         r1.index_name || '  rebuild';      
    end loop;
    
    dbms_stats.gather_table_stats(ownname=>g_owner,tabname=>g_table_name,
                                  estimate_percent=>33,cascade=>true);
    
end P_TABLE_MAINTENANCE;

FOLLOW UP: (23.11.2010) With Oracle10g there is a new way of reorganising your table segments. The best thing is there is not any unusable indexes anymore.

select owner, table_name, 
  trunc((avg_row_len*num_rows)/1024/1024) as ROWS_MB, 
  trunc((blocks*8192)/1024/1024) as TOTAL_MB 
  from dba_tab_statistics where table_name = 'WIZ_CUSTOMER_MAILBOX';
--OWNER, TABLE_ANME, ROWS_MB, TOTAL_MB
--PROD_DBA,WIZ_CUSTOMER_MAILBOX,19982,29790

alter table PROD_DBA.WIZ_CUSTOMER_MAILBOX enable row movement;
alter table PROD_DBA.WIZ_CUSTOMER_MAILBOX shrink space compact;

select owner, table_name, 
  trunc((avg_row_len*num_rows)/1024/1024) as ROWS_MB, 
  trunc((blocks*8192)/1024/1024) as TOTAL_MB 
  from dba_tab_statistics where table_name = 'WIZ_CUSTOMER_MAILBOX';
--OWNER, TABLE_ANME, ROWS_MB, TOTAL_MB
--PROD_DBA,WIZ_CUSTOMER_MAILBOX,19982,26790