Re: Re-org of partitioned tables

From: Jaap W. van Dijk <j.w.vandijk.removethis_at_hetnet.nl>
Date: Wed, 08 Apr 2009 20:29:57 GMT
Message-ID: <49dd0733.20620046_at_news.hetnet.nl>

On Wed, 8 Apr 2009 09:21:33 -0700 (PDT), Nag Yarlagadda <ynagarjuna_at_gmail.com> wrote:

>I have strange situation.I have table which is already partitioned by
>range.This table has partitions upto 2007 November.And there after the
>rest of the records are moving into MAX partition.Now we are planning
>to split the max partition so that access of data will be more
>ease.With huge amount of data(250 million records) in the max
>partition its taking around 40 to 60 minutes for splitting the
>partition.And during the process the indexes on the table are getting
>invalid.So i am looking at better way of doing the split.Is it
>possible to do re-org on the max partition and split it using online
>redefinition?

Do you have global indexes? If you have local indexes, and assuming you can do the following while the contents of the max-partition is not changing:

• create two tables similar to the partitioned table (including the indexes)
• fill table 1 with the records that should go into future partition 1
• fill table 2 with the records that should go into future partition 2
• etc.

This will take some time, but this does not change the contents of the max-partition. The folllowing steps are only administrative and will be performed very quickly:

• truncate the max-partition
• split the max-partition (resulting in partition 1, 2, etc.)
• perform an EXCHANGE of table 1 with partition 1
• perform an EXCHANGE of table 2 with partition 2
• etc.

Regards,
Jaap. Received on Wed Apr 08 2009 - 15:29:57 CDT