Question
Which of the following is the best way to migrate a large data load from one table to another, considering total time and blocking?
- A. Split the load size into many transactions. Correct
- B. Split the load size in half and run simultaneously.
- C. Batch into small loads and run in parallel.
- D. Batch large loads into one transaction.
Explanation
Splitting a large load into many smaller transactions keeps each transaction short, so locks are held briefly, lock escalation and log growth are limited, and other sessions are not blocked for long, while a failure only rolls back one small batch. One large transaction holds locks for the whole load and blocks other users, and running halves or small batches in parallel against the same table makes the loads compete for locks and block each other.
