New Oracle Magazine Article on DML Error Logging

"Faster Batch Processing" : "When you need to load millions of rows of data into a table, the most efficient way is usually to use an INSERT, UPDATE, or MERGE statement to process your data in bulk. Similarly, if you want to delete thousands of rows, using a DELETE statement is usually faster than using procedural code. But what if the data you intend to load contains values that might cause an integrity or check constraint to be violated, or what if some values are too big for the column they are to be loaded into?.. DML error logging enables you to write INSERT, UPDATE, MERGE, or DELETE statements that automatically deal with certain constraint violations. With this new feature, you use the new LOG ERRORS clause in your DML statement and Oracle Database automatically handles exceptions, writing erroneous data and details of the error message to an error logging table you've created."

This is my new article for Oracle Magazine where I take a look at the new DML Error Logging feature in Oracle Database 10g Release 2, that lets you gracefully handle errors that occur when you try and do an INSERT, UPDATE or DELETE on a table. Prior to 10gR2, if you were doing a bulk insert of data into a table and one row of the source data caused a constraint to fail, the whole statement rolled back and you had to revert to row-based procedural code; with this new feature, you can use the DBMS_ERRLOG.CREATE_ERROR_LOG procedure to create an error logging table, run your INSERT /*+ APPEND */ statement and the rows that would have caused the INSERT to fail will instead be written to the error logging table. There are a few exceptions where DML Error Logging won't catch all errors, but it's a pretty cool feature and it's going to change the way that you perform ETL within the database.

There's a bit of an interesting story attached to the article as well, in that I did some timings around using DML Error Logging with direct path insertions, conventional path insertions, and row-based insertions using BULK COLLECT, FORALL and SAVE EXCEPTIONS, and as I expected found that the direct path route was faster than both conventional path and row-based code; however, the conventional path route was slower than even the row-based code and when I passed the article around a few people for review prior to submission, Tom Kyte picked up this and suggested I run it past Jonathan Lewis. If you're interested in the story I wrote it up here at the time , but the long and the short of it was that Jonathan spotted that when conventional path was used, the "block at a time" optimisation for undo and redo disappeared, in effect meaning that conventional path switches to a single-row mode, making it take twice as long as direct path mode even when no errors occurred in the data. When the article went through the review process at Oracle, the official explanation that came back was that "LOG ERRORS clause ... causes kernel device table (KDT) buffering to be disabled when you're performing a conventional-path INSERT" which appears to be "by design" rather than a bug - I guess it's something that would take a bit of work to code around. Still it's worth bearing in mind when using DML Error Logging - it's only really worthwhile if you can do your insert in direct path mode.

Anyway, the article's here if you want to take a look, and whilst you're there there's a good feature article on Business Intelligence called "The Face of Intelligence" which looks at some customer case studies and comes with a handy updated PDF of Oracle's Business Intelligence product architecture.