Convert non-partitioned table to partitioned table using DBMS_REDEFINITION

I had a request from my users that suddenly one of the query is running slowly. I did the usual checks, SQL Plan regression, storage response time, load on the database, there was nothing unusual. Then I told my user that the elapsed time for this query is as usual and the plan has not changed however I have some ideas to improve the query performance. I mentioned that he should consider partitioning the table as the table has 500 million rows and the size is around 120G, then he confessed that this table was supposed to be partitioned but he somehow forgot and asked me if I can magically partition the table with no downtime. This is when I started looking at DBMS_REDEFINITION which among several other things allows you to convert non-partitioned table to partitioned table. Following are the easy to understand steps to partition your table, fully ONLINE no downtime required

Step 1) Check if the table can be redefined

exec DBMS_REDEFINITION.CAN_REDEF_TABLE('PKOTHURI','SITEVIEW_DATA_50M',dbms_redefinition.cons_use_rowid);

you can use also use the primary key to redefine the table, use dbms_redefinition.cons_use_pk instead of dbms_redefinition.cons_use_rowid

Step 2) Create the interim table with partitioning scheme, no need to define constraints, indexes, triggers as they will be copied

 CREATE TABLE "SITEVIEW_DATA_50M_INT" (
 "ColumnId" NUMBER(38,0) DEFAULT 0 NOT NULL ENABLE,
 "SiteId" NUMBER(38,0) NOT NULL ENABLE,
 "Status" VARCHAR2(64 BYTE) DEFAULT 'n/a',
 "Value" NUMBER(38,2),
 "URL" VARCHAR2(512 BYTE) DEFAULT NULL,
 "Color" VARCHAR2(64 BYTE),
 "Time" TIMESTAMP (8),
 "EndTime" TIMESTAMP (8),
 "ColorId" NUMBER(38,0),
 "CompressAlgorithmId" NUMBER(38,0),
 InsertTime TIMESTAMP
 ) PARTITION BY RANGE("Time")
 INTERVAL(NUMTOYMINTERVAL(1, 'MONTH')) (
 PARTITION SD11Y10 VALUES LESS THAN (TO_DATE( '01-DEC-2010', 'DD-MON-YYYY')),
 PARTITION SD12Y10 VALUES LESS THAN (TO_DATE( '01-JAN-2011', 'DD-MON-YYYY'))
 ); 

Step 3) Start the redefinition process

 BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE('PKOTHURI','SITEVIEW_DATA_50M','SITEVIEW_DATA_50M_INT',options_flag=>dbms_redefinition.cons_use_rowid);
END;/ 
Step 4) Copy dependent objects (like triggers, indexes, materialized views, grants and constraints)
 DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('PKOTHURI','SITEVIEW_DATA_50M','SITEVIEW_DATA_50M_INT', DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);
END;/ 
Step 5) Check for any redefinition errors, e.g unable to copy the constraint
 select object_name, base_table_name, ddl_txt from DBA_REDEFINITION_ERRORS; 
Step 6) Synchronize the table, it is important to do this if you are redefining a large table in busy production environment
 exec DBMS_REDEFINITION.SYNC_INTERIM_TABLE('PKOTHURI','SITEVIEW_DATA_50M','SITEVIEW_DATA_50M_INT'); 

Step 7) Complete the redefinition, this is when the switch happens and this is a BLOCKING operation

 exec DBMS_REDEFINITION.FINISH_REDEF_TABLE('PKOTHURI','SITEVIEW_DATA_50M','SITEVIEW_DATA_50M_INT'); 

How to make it faster?

If you are redefining a very large table then START_REDEF_TABLE step can take lot of time, you can speed up if you put the intermediate table in nologging mode which disables redo generation on the intermediate table. In my testing putting the intermediate table is atleast 2.5x faster, if you have force logging enabled at the database level which will be the case if you have DataGuard then nologging has no effect

 
alter table "SITEVIEW_DATA_50M_INT" nologging;
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE('PKOTHURI','SITEVIEW_DATA_50M','SITEVIEW_DATA_50M_INT',options_flag=>dbms_redefinition.cons_use_rowid);
END;/ 
alter table "SITEVIEW_DATA_50M_INT" logging;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s