Reorganizing Tables in Oracle – is it worth the effort?

Reorganizing Tables in Oracle – is it worth the effort?

if the intention of the reorganizing operation is to gain space resp. “defragment” the table, this operation is very likely just a waste of effort & resources. I will try to illustrate my point with a simple demonstration that anybody with access to an Oracle Database can easily reproduce. My demo Database is 11gR2, but the same can be done with 10g also. If you are on an even older version, there is no SHRINK SPACE available, so you would have to use MOVE instead. I prepare a little demo Table with 1Mio rows now – the time_id population was the hardest part of that demo for me 

SQL> create table sales as
select
'Oracle Enterprise Edition' as product,
mod(rownum,5) as channel_id,
mod(rownum,1000) as cust_id ,
5000 as amount_sold,
to_date
('01.' || lpad(to_char(mod(rownum,12)+1),2,'0') || '.2010' ,'dd.mm.yyyy')
as time_id
from dual connect by level<=1e6;  Table created.  SQL> select time_id ,count(*)
from sales group by time_id
order by 1;

TIME_ID     COUNT(*)
--------- ----------
01-JAN-10      83333
01-FEB-10      83334
01-MAR-10      83334
01-APR-10      83334
01-MAY-10      83334
01-JUN-10      83333
01-JUL-10      83333
01-AUG-10      83333
01-SEP-10      83333
01-OCT-10      83333
01-NOV-10      83333
01-DEC-10      83333

12 rows selected.

SQL> select segment_name,bytes/1024/1024 as mb from user_segments;

SEGMENT_NAME                 MB

-------------------- ----------

SALES                        54

The table contains about 83000 rows per month. Now I will delete the first quarter of rows:

SQL> delete from sales where time_id<to_date('01.04.2010','dd.mm.yyyy');

250001 rows deleted.

SQL> commit;

Commit complete.

SQL> select segment_name,bytes/1024/1024 as mb from user_segments;

SEGMENT_NAME                 MB
-------------------- ----------
SALES                        54

This is the starting point of a possible reorganization: Although 250k rows got deleted, the table consumes the same space as before. In other words: The High Water Mark did not move. A reorganization would move the High Water Mark and would regain the space that was consumed by the 250k rows, like shown in the below picture:

The question is: Is that necessary? If inserts would take place after the deletion again, then the space would get reused without any need to reorganize:

SQL> insert into sales
select
rownum as id,
mod(rownum,5) as channel_id,
mod(rownum,1000) as cust_id ,
5000 as amount_sold,
to_date
('01.' || lpad(to_char(mod(rownum,3)+1),2,'0') || '.2011' ,'dd.mm.yyyy')
as time_id
from dual connect by level<=2.5e5;

250000 rows created.

SQL> commit;

Commit complete.

SQL> select time_id ,count(*)
from sales group by time_id
order by 1;  

TIME_ID     COUNT(*)
--------- ----------
01-APR-10      83334
01-MAY-10      83334
01-JUN-10      83333
01-JUL-10      83333
01-AUG-10      83333
01-SEP-10      83333
01-OCT-10      83333
01-NOV-10      83333
01-DEC-10      83333
01-JAN-11      83333
01-FEB-11      83334
01-MAR-11      83333

12 rows selected.

I inserted a new quarter of rows. The table remains in the same size as before:

SQL> select segment_name,bytes/1024/1024 as mb from user_segments;

SEGMENT_NAME                 MB
-------------------- ----------
SALES                        54

That is exactly the point I like to emphasize: If you have inserts following deletes, reorganization of tables is not necessary! Only if that is not the case (Table gets no longer inserts after deletion), you may reorganize it:

SQL> delete from sales where time_id<to_date('01.07.2010','dd.mm.yyyy');  250001 rows deleted. SQL > commit;
Commit complete.

SQL> alter table sales enable row movement;

Table altered.

SQL> alter table sales shrink space;

Table altered.

SQL> select segment_name,bytes/1024/1024 as mb from user_segments;

SEGMENT_NAME                 MB
-------------------- ----------
SALES                     40.25

The space consumed by the table got reduced now and is usable for other segments. Although the table is not locked during the SHRINK SPACE operation and users can do DML on the table as usual, the operation is not “for free” in terms of resource consumption. It does a lot of I/O, creates a lot before images that consume space in the UNDO tablespace and the operation modifies many blocks, so lots of redo protocol (and therefore many archived logs, probably) gets generated.

If you really think that you need that kind of reorganization regularly, you should probably evaluate the Partitioning Option here:

SQL> create table sales_part
(product char(25), channel_id number,
 cust_id number, amount_sold number, time_id date)  
partition by range (time_id)  
interval (numtoyminterval(3,'month'))
(partition p1 values less than (to_date('01.04.2010','dd.mm.yyyy')))
;

Above command created a table that is partitioned by the quarter. We could simply drop a partition instead of deleting lots of rows and we would never need to reorganize to regain space here. If you are on 10g, the feature INTERVAL PARTITIONING is not available there. You can then use RANGE PARTITIONING with the additional effort that you need to create the necessary range partitions manually.

Conclusion: Before you decide to reorganize  a table, make sure that this is really necessary, because likely it isn’t