Friday, September 04, 2009

11gR2 nuggets

Haven't installed 11gR2 yet, but found a couple of nuggets in the documentation.

Firstly, WAIT_ON_PENDING_DML in DBMS_UTILITY.
Image this situation. At 11:59, transaction A inserts a record into a table with a CREATED_ON of SYSDATE, but is not yet committed. At 12:00 a batch job kicks off to pick out all the new records, using the CREATED_ON date. Since the one from transaction A isn't committed yet, it is missed by the batch job. If the batch job says "I picked up everything dated before 12:00" it will miss it next time too.
Previously, you might work around this with v$transaction or queuing. A long time ago I even used a LOCK TABLE IN EXCLUSIVE MODE. It was immediately released after it had been obtained but still provided a potential for an issue (though it isn't so dangerous if you add a WAIT 5 on the end). Now you can simply tell your transaction to wait until any other transaction on the table that started before 12:00 has completed.

Secondly, having the option of an EXIT in SQL*Plus default to quitting with a ROLLBACK rather than a COMMIT.
It is not one I'd thought was an issue, but it came up on Stackoverflow at the same time. Coincidence, I guess. Anyway, I'd expect Instant Clients for 11gR2 to come out fairly soon, so this can be put into production a lot quicker. Be warned though, a DISCONNECT or CONNECT will (apparently) still do an implicit commit. That all seems odd to me, as mentally I'd expect an EXIT to do a disconnect from the database session then exit the client (and a CONNECT to do a disconnect from one session and then a connect to a new one) and that any COMMIT/ROLLBACK option would relate to the disconnect option.

Thirdly, hints.
I'm not sure I like this but IGNORE_ROW_ON_DUPKEY_INDEX allows the statement to silently ignore ORA-0001 errors. The row won't be inserted, but the SQL statement won't fail either. Not sure if AFTER ROW triggers will fire or not. Personally I prefer the thought of the error logging clause for this sort of functionality. I just don't like hints which change what the statement does at a logical level.

One hint I want to experiment with is APPEND_VALUES, which is like the APPEND hint, but for FORALL...INSERT in PL/SQL. Not sure how often you would have an array in memory large enough to benefit from this. Also, once you've done a direct-path insert, the table wants a commit/rollback before you can do another. If APPEND_VALUES is the same, you'd need to introduce a commit if you want to use this for looping big chunks of data.