Rittman Mead - Delivered Intelligence

"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.

Posted in Oracle Database |