I would like to move billions of rows from schema1.table1 to new schema2.table2 where table2 is a refactored one from table1. Hence their table structure is different. both table1 and table2 are partitioned but table2 is empty. Both these schemas are in the same oracle DB. What is performance efficient way to perform this data migration? Would you want to perform commit only at the very end or opt for incremental commit? i.e. let's say data migration fails after completing 99% of the job which took few hours. Do you rollback now? If you do the incremental commit, how do you handle the failure?
Add a comment
|
2 Answers
Parallel INSERT APPEND with NOLOGGING would be the way to do this, then as with all NOLOGGING operations, take a backup immediately on completing. Mark indexes unusable first, disable constraints, alter table, perform the operation, then re-enable constraints etc.
Append causes Oracle to always grab free space above the current high water mark, so it's not efficient at reusing space in the segment, but it avoids fiddling with the freelist and the UNDO overhead. If you have to start again for any reason, TRUNCATE, don't DELETE.
As to the incremental commit, it will depend on how your data is segmented, can you easily say move a month's worth at a time (e.g. is the partitioning scheme the same in source and target)? Because remember that if you need to satisfy some predicate, that will obviously slow you down. Test to make sure the operation isn't going to fail logically (e.g. incompatible datatypes in source and target) then allocate sufficient resources and just go for it in one transaction. Good luck!
-
I know using online redef is going to be slow but dbms_redef may not even support the above scenario?– JohnMar 25, 2011 at 5:04
if the partition scheme is the same (data of partion a in table 1 goes to partition a in table 2 etc.) then I would go for multiple sessions and have each session append their data in their 'own' partition. This prevents a lot of locking and has the best speed. Depending on the hardware you can fill the HBA cards up until their neck. A commit for every partition - assuming more than a few rows for each partition - won't be a problem and I would certainly do so. Assuming that the application is down during the migration, the fallback is simple: don't change the app and truncate the partitions of table2 before trying again, at least for those parts where the app changed the data before a second run could take place.
I hope this helps