Best approach for executing massive insert select from statements

Question

I have over 100k insert select from statements which I will break into smaller batches. I came up with two approaches and not sure which one is more effective. Please give me your thoughts.

I could use APPEND hint but I have to put a Commit after each insert statement due to ORA-12838: cannot read/modify an object after modifying it in parallel.

Or

I could remove the hint and go for a conventional path load with just one commit in the end of each batch load.

Conventional path load will definitely generate a lot of logs. But will committing after each insert statement be better or worse in terms of performance?

Answer 1

Running all queries with a single commit will use a lot of log. And, if anything goes wrong, the rollback could be very expensive. So I'd discard that option.

And I'd start with one commit after each statement. Why? Because it is simple.

After running, if the process is too slow, add an ability to commit after some number of statements, every 1,000 statements seems a good place to start. You can then tweak that number to see where you get the best performance.