Hi Friends,
Recently for one of the functionality in my app, I was working on the bulk data operation. To handle the bulk data movement with DML exception handling Oracle Provides us two inherent functionality,
So, I was evaluating on both the approaches, here is what I have came up to so far,
Performance for Save Exceptions Vs DML Error Logs,
a. Save exception works better for the large data volume over DML Error logs.
- You can keep better control on the number of rows been process in particular iteration by configuring Bulk collect row count limit.
- This will help you in keeping the resources free by putting the commit after particular/Logical interval.
b. For lesser size of the data we can go with DML Error log option.
- Using DML error log with Direct path inserts gives us the optimal performance over processing row by row in a loop.
When to use Save Exceptions and when DML Error Logs? Why?
Master of Oracle, Thomas Kyte (Tom) Says,
“When you HAVE to do row/row (also known as slow by slow) processing. When you have to read data, procedurally process data, insert data. Then use save exceptions otherwise use a single sql statement”
Why can’t we use GTT as an Error log table?
One more problem that has come across is, in DML error log, can we use Oracle Global Temporary table as error log table? (As, I don’t want to expose the errors to other sessions).
After a small investigation, found the answer is NO. Oracle internally uses autonomous transaction to log the errors in Error table. And that will not be visible to your session. Hence need to use real table only.
Comments
Post a Comment