Re: Catalogue of SQL and PL/SQL bad practices: Call for participation

On Thu, 20 Dec 2007 10:54:13 -0800 (PST), Gojko Adzic <gojkoa_at_gmail.com> wrote:

>HI Daniel,
>
>thanks. Could you be a bit more specific about the cursor loop? I've
>used a cursor loop often to do batch updates during busy trading
>hours, so that I keep locking at minimum level and not interrupt
>trading applications. I don't necessarily consider that as a bad use,
>since a single long update would collide with lots of sessions and
>cause deadlocks. Is that the kind of usage you do not like? If not,
>what bad experiences have you had with it, how would you rate the
>problem (performance?).

So far for the document of bad practices. As Oracle by default issues row level locks and never escalates a lock your issue is a non-issue, and the reasons you provide are myths.

Rule 1 is 'Anything that can be done in pure SQL, should be done in pure SQL'
If it can not be done in SQL, using BULK UPDATE etc is mandatory. The reason for this is for every fetch there will be a context switch from PL/SQL engine to SQL engine and vice version, and likely there will be a sqlnet roundtrip.

Commandment 2 is
'Thou shalst not COMMIT inside a LOOP, and thou shalst not COMMIT every n records inside a LOOP.

Commandment 1 in this forum is:
As Humans happen to read from top to bottom, thou shalst not top post. This includes the Black Riders of the Evil Empire called Microsoft.

-- 
Sybrand Bakker
Senior Oracle DBA