Big Turn-off for DML Error Logging

[pbarmak]

I was really impressed with 10g's DML Error logging feature (where one can used bulk loads and have common errors go into an ERR$_ table without raising the error and rolling back the data). But then, when looking to use the feature in a real life situation, we ran into this statement from the Oracle DW Docs:

"The following conditions cause the statement to fail and roll back without invoking the error logging capability: ...
* Any direct-path INSERT operation (INSERT or MERGE) that raises a unique constraint or index violation"

Boy, that stings. When loading millions of rows into a fact table, we NEED to use direct-path inserts and we also need to have local unique indexes and catch duplicates on those columns. So we can't use DML Error Logging in these cases - the statement will just rollback as usual.

I'd like to get some expert thoughts on this - what workarounds are there for us to use this nifty new feature? Or, if we can't use a logging table, what are the best alternatives to handling unique constraints while still doing direct-path loads (insert /*+ append */). It seems PL/SQL doesn't give us a perfect option (even with forall, it's not direct path).

Thanks.

[hrishy]

Hi

Well yes thats a big turn off i agree..


But in our shop we use a flat file to load the fact table and we use good ol sql loader so any unique constraint violations are taken care of by the errors equals parameter of the control file.


How are your fact tables loaded ?


regards
Hrishy

[slimdave]

If it's errors within the new data alone then it's pretty easy -- use an analytic function to detect the dupes and a multitable insert to write the dupes to a different table. Does error logging work with multitable insert?

If the dupes could be from combining the new and old data then select from a union all that includes the data with which the new data could have dupes and do the same analytic function ju-ju, but tag the rows read from the target table so they can be filtered out without being reinserted.

[OracleDoc]

Hmmm, flat file= external table.

Why would anyone one want to use SQL loader these days? Also, if you were to load the flat file as an external table wouldn't be easier to get rid of any dupes before loading?

[slimdave]

Hmmm, flat file= external table.

Why would anyone one want to use SQL loader these days?

One (purely theoretical) reason would be that an organisation that controls o/s level access won't allow you to place data files on the host system, so they have to be loaded from a client.

just sayin'

[pbarmak]

If it's errors within the new data alone then it's pretty easy -- use an analytic function to detect the dupes and a multitable insert to write the dupes to a different table. Does error logging work with multitable insert?

If the dupes could be from combining the new and old data then select from a union all that includes the data with which the new data could have dupes and do the same analytic function ju-ju, but tag the rows read from the target table so they can be filtered out without being reinserted.

These are not bad options, but I wonder what the performance hit would be - our goal would be to not try and do checks within the SQL or use the target table within a union all as I would guess there is a big performance hit there. Since we're comparing against the target table (well, target partition of the target table), we could be bringing in many millions of rows just to do the check - and most of the time, that check would come out clean (no dups).

Oh, and no, we're not using sql*loader - external tables are our method. We can clean the dups in the specific external table, but it's dups from previous loads in the target fact table I'm concerned about (may not necessarily come from the same load).

BTW, I have a forum gripe - I'm not getting any notifications from this forum - I have it on instant and my email address is right. Is it just poor ol' me?