Re: Re-org of partitioned tables

From: Jaap W. van Dijk <>
Date: Wed, 08 Apr 2009 20:29:57 GMT
Message-ID: <>

On Wed, 8 Apr 2009 09:21:33 -0700 (PDT), Nag Yarlagadda <> 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

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.

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

Original text of this message