Fast data migration on the same database

Question

I'm trying to find a way to perform a migration from two tables on the same database. This migration should be as fast as possible in order to minimize the downtime.

To put it on an example lets say I have a person table like so:

person_table -> (id, name, address)

So a person as an Id, a name and an address. My system will contain millions of person registries and it was decided that the person table should be partitioned. To do so, I've created a new table:

partitioned_person_table->(id,name,address,partition_time)

Now this table will contain an extra column called partition_time. This is the partition key for this table since this is a range partition (one partition every hour).

Finally, I need to find a way to move all the information from the person_table to the partitioned_person_table with the best performance.

• The first thing I could try is to simply create a statement like:

  INSERT INTO partitioned_person_table (id, name, address, partition_time) SELECT id, name, address, CURRENT_TIMESTAMP FROM person_table;

The problem is that when it comes to millions of registries this might become very slow (also the temporary tablespace might not be able to handle all this information)

• My second approach was to use the EXCHANGE PARTITION method. Unfortunetly, I cannot do this because the tables contain diffrent column numbers.

Is there any other way that I can perfom this with the best performance (less downtime) ?

Thank you.

Answer 1

If you can live with the state, that all the current records would be located in one partition (and your INSERT approach suggest that), you may only

1) add a new column partition_time either as NULL or possible with metadata default only - required 12c

2) switch the table to a partitioned table either with online redefinition (if you have no maintainace window, where the table is offline) or with exchange partition otherwise.