Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> Repartion 500mil Row Table
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, TexasReceived on Fri Jul 14 2000 - 14:07:35 CDT