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?
Large data movement
oracle
Related Question
- What’s the most effective way to back-up / save data on Oracle 11g large data sets
- Failed to drop column – value too large
- Movement of Standby database from one location to another location
- Oracle – Deleting Large Amounts of Data Efficiently
- Oracle – large table with large IN
- Performance Recommendations for Large Volume of Data
- How to export large table data into different files in oracle
Best Answer
Parallel
INSERT APPENDwithNOLOGGINGwould 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'tDELETE.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!