Tuesday, October 9, 2007

Database Reorganization plan based on Alter Table

I want to share my experience with alter table move tablespace. I was handling a project to migrate data mart database tables from dictionary managed tablespace to locally managed tablespace.
Database version:

The m_gnap_records table size was around 40GB and it had 210 columns. When I started this assignment, the existing plan was based on the following approach:

alter table m_gnap_records move tablespace nologging;

Time taken: 6 hours 20 minutes

I changed the above plan to the following:

a. alter table m_gnap_records nologging;
b. alter table m_gnap_records move tablespace s_u_data nologging parallel 4;

Time Taken: 1 hour 55 minutes.

The logic was to disable the logging at the object level then use nologging for the alter table operation.

I tested the following also to verify the approach:

alter table m_gnap_records logging;
alter table m_gnap_records move tablespace s_u_data_test nologging parallel 4;

Time taken : 4 hours 48 minutes