Non-Partition To Partition Table

In reviewing the table structures and SQL statements executed within the database over the 24 hour period, it is apparent partition on certain tables that are date driven will be beneficial for performance by allowing for partition elimination. Identify candidates that are good candidates for partitioning is the easy part. Change them is a little more challenging.

There are three methods that I have seen used to change non-partition tables to partitioned.

A. Starting with the most cumbersome method:
1. Create an interim table with all the dependents (constraints, triggers, indexes).
2. Copy the data to the new table. CTAS would work, but you would then need to create the dependents.
3. Drop the original table.
4. Create the new table with the old name including all the dependents with the standard names with the partitioning information.
5. Copy the data back from the interim table to the new partitioned table.
6. Don’t forget statistics!

B. Next is Exchange partition:
1. Create a Interim table (this will be the same interim table that we use later as well for dbms_redefinition. There is one difference.
The partition below has maxvalue which will allow all the contents of the original data to transfer to this partition. We will handle splitting the data into additional partitions at the end.


CREATE TABLE "TEST"."TABLE1"
( "ID" NUMBER NOT NULL ENABLE,
"DETAIL" NUMBER(15,0) NOT NULL ENABLE,
"TEXT" VARCHAR2(4000),
"REC" VARCHAR2(4) NOT NULL ENABLE,
"REC_" VARCHAR2(2000),
"START_DT" DATE NOT NULL ENABLE,
"STATUS" VARCHAR2(20) NOT NULL ENABLE,
"MESSAGE" VARCHAR2(4000),
CONSTRAINT "TABLE1_PK1" PRIMARY KEY ("ID", "DETAIL")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "INDX" ENABLE,
CONSTRAINT "TABLE1_FK1" FOREIGN KEY ("ID")
REFERENCES "TEST"."LOG" ("ID") ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 20 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
PARTITION BY RANGE (START_DT)
(PARTITION P2013 VALUES LESS THAN (MAXVALUE)) )
TABLESPACE "USERS" ENABLE ROW MOVEMENT "

3. Create indexes and constraints that may not have been included in the original script above. Keep in mind this will be unique to your situation.

create index TEST.IDX1 on TEST.TABLE1_PART(start_dt) Local;

create index TEST.IDX2 on TEST.TABLE1_PART(detail) local;

4. Now we want to exchange the contents of the two tables

ALTER TABLE test.table_1
EXCHANGE PARTITION p2013
WITH TABLE test.table
WITHOUT VALIDATION
UPDATE GLOBAL INDEXES;

5. Drop the original table TEST.TABLE and rename all the constraints and indexes. You will need to handle the referential constraints from other tables as well.

6. Now to add the appropriate partitions

ALTER TABLE big_table
SPLIT PARTITION p2013 AT (TO_DATE(‘ 2013-01-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
INTO (PARTITION p201301,
PARTITION p2013)
UPDATE GLOBAL INDEXES;

ALTER TABLE big_table
SPLIT PARTITION p2013 AT ((TO_DATE(‘ 2013-02-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
INTO (PARTITION p201302,
PARTITION p2013)
UPDATE GLOBAL INDEXES;

continue until you have created all the necessary partitions for your data.

7. Verify the results.

C. Lastly, DBMS_REDEFINITION — this has become my preferred method. You still have to create the interim table, but it is what it is at this point. Wouldn’t it be nice if we could just some how magical make partitions on an existing table with having to create the interim table.

1. Determine partition scheme, to include naming, and partition key.
2. Create an interim table with the partition information included. This can be easily handled by:

select dbms_metadata.get_ddl('TABLE','TABLE NAME') from dual;

The output will be similar to this:


CREATE TABLE "TEST"."TABLE1"
( "ID" NUMBER NOT NULL ENABLE,
"DETAIL" NUMBER(15,0) NOT NULL ENABLE,
"TEXT" VARCHAR2(4000),
"REC" VARCHAR2(4) NOT NULL ENABLE,
"REC_" VARCHAR2(2000),
"START_DT" DATE NOT NULL ENABLE,
"STATUS" VARCHAR2(20) NOT NULL ENABLE,
"MESSAGE" VARCHAR2(4000),
CONSTRAINT "TABLE1_PK" PRIMARY KEY ("ID", "DETAIL")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "INDX" ENABLE,
CONSTRAINT "TABLE1_FK" FOREIGN KEY ("ID")
REFERENCES "TEST"."LOG" ("ID") ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 20 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ENABLE ROW MOVEMENT "

The above create statement needs to be modified to change the names of the created objects since they already exist. I’m not using the new interval partition in 11g simply because of the manner in which we archive and name the archive files. Remember this an interim table.


CREATE TABLE "TEST"."TABLE1"
( "ID" NUMBER NOT NULL ENABLE,
"DETAIL" NUMBER(15,0) NOT NULL ENABLE,
"TEXT" VARCHAR2(4000),
"REC" VARCHAR2(4) NOT NULL ENABLE,
"REC_" VARCHAR2(2000),
"START_DT" DATE NOT NULL ENABLE,
"STATUS" VARCHAR2(20) NOT NULL ENABLE,
"MESSAGE" VARCHAR2(4000),
CONSTRAINT "TABLE1_PK1" PRIMARY KEY ("ID", "DETAIL")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "INDX" ENABLE,
CONSTRAINT "TABLE1_FK1" FOREIGN KEY ("ID")
REFERENCES "TEST"."LOG" ("ID") ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 20 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
PARTITION BY RANGE (START_DT)
(PARTITION "P201301" VALUES LESS THAN (TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION "P201302" VALUES LESS THAN (TO_DATE(' 2013-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) )
TABLESPACE "USERS" ENABLE ROW MOVEMENT "

3. begin dbms_redefinition.start_redef_table(‘TEST’,’TABLE1′,’TABLE1_PART’); end;
— this begins the redefinition process

4. begin dbms_redefinition.sync_interim_table(‘TEST’,’TABLE1′,’TABLE1_PART’); end;
— this synchronizes the interim and the original table. It may or may not be needed
depending on how long the process takes. I tend to error on the safe side and just
include it.

5. Any indexes on the interim table need to be re-created as well, but with different names.
The PK and FK indexes have already been taken care of when we created the interim table.

create index TEST.IDX1 on TEST.TABLE1_PART(start_dt) Local;

create index TEST.IDX2 on TEST.TABLE1_PART(detail) local;

6. begin dbms_redefinition.finish_redef_table(‘TEST’,’TABLE1,’TABLE1_PART’); end;

The interim table will now have the original table’s name: TABLE1
The original table will now have the interim table’s name: TABLE1_PART

A simple describe on both tables will show the switch of the structures. You can also verify the partitions:
select partitioned on dba_tables where owner = ‘TEST’;
There should be one record returning displaying YES.

select partition_name from dba_tab_partitions where table_name = ‘TABLE1’;
This will list out the partitions that were created for the table.

7.  Drop referencing foreign keys and prepare the re-create script which will point to the renamed constraint in step 9.   This is necessary to drop the interim table.

On a side note for this particular step, although I personally believe that all steps should be scripted out prior to beginning any maintenance, this is especially true for this step.  Once you drop the constraints they are gone.  In my case this would be very troubling if I didn’t have a script ready to re-create the constraints since one of the tables I’m working on has 40 plus tables that reference its primary key constraint.  In a fut

8. Drop the interim table
drop table test.table1_part;

9. Remember that we renamed all the constraints and indexes on the interim table so that they wouldn’t conflict
with the original table. Now that the interim table (the original table) has been dropped along with all their dependencies, we can rename the constraints, and indexes appropriately.

ALTER TABLE TABLE1 RENAME CONSTRAINT TABLE1_PK1 to TABLE1_PK;
ALTER INDEX RENAME CONSTRAINT TABLE1_FK1 to TABLE1_FK1;
ALTER INDEX TABLE1_PK1 RENAME TO TABLE1_PK;
ALTER INDEX TABLE1_FK1 RENAME to TABLE1_FK;
ALTER INDEX IDX1 RENAME to IDX;
ALTER INDEX IDX2 RENAME to IDX1;

9. Verify the changes:
select * from dba_constraints where table_name = 'TABLE1';
select * from dba_indexes where table_name = 'TABLE1';

So we have covered the three different methods I have used to convert a non-partitioned table to a partition table. As I stated, the third option DBMS_REDEFINITION is the method that I use without issue. There are other commands and other situations where DBMS_REDEFINITION would be of use.

Advertisements

Tagged: , ,

Leave a Reply

Please log in using one of these methods to post your comment:

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

%d bloggers like this: