Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Repartion 500mil Row Table

Repartion 500mil Row Table

From: Jack C. Applewhite <>
Date: Fri, 14 Jul 2000 14:07:35 -0500
Message-Id: <>

One of our telecom clients has a large 7x24 OLTP database centered around a 500,000,000 row partitioned table of customer phone calls growing at 100,000,000 rows per month.
The table is currently range partitioned on the call origination time - each partition is one calendar month. For business reasons they want to change the patition key to the call ending time. This would mean that the calls starting just before midnight (GMT) at the end of a month, but ending after midnight, would now be migrated to the next partition.

They have limited outage windows and want to repartition as quickly as possible during one of them. We see two main possibilities: 1. Create the new partitioned table and Insert into it as Select from the current table. Of course this is the most time-consuming option, not to mention rebuilding the 4 indexes on the table.
2. Partition Exchange each current partition into a table, then Exchange that table into a partition of the new partitioned table (with the No Validate option). The local indexes would also be partition exchanged along with the table partitions. A simple update statement on the new partition key column (update to itself) for those few rows that don't belong in a new partition would cause Oracle to migrate the rows to the correct partitions (repartitioning will be done after they upgrade from 8.0.5 to 8.1.6).

Would #2 be significantly faster? Anybody done something like this? Any "gotchas" wrt Partition Exchange? Any suggestions for a better method?

TIA Jack

Jack C. Applewhite
Senior Consultant, OCP Oracle8 DBA
Stonebridge Technologies, Inc.
Austin, Texas
Received on Fri Jul 14 2000 - 14:07:35 CDT

Original text of this message