Re: PL/SQL - writing better code

On Oct 19, 2:35 pm, "RanLi" <gladp..._at_hotmail.com> wrote:
> hi
> need to run some thoughts about coding in pl/sql by someone, and you
> guys seem to be the closest. :)
>
> PROBLEM/SITUATION:
> i have a procedure that loops through a set of keys. I update a column
> for each key.
> LOOP ----
> BEGIN
> <update statement>
> COMMIT;
>
> EXCEPTION
> WHEN OTHERS
> THEN
> ROLLBACK;
> END;
> END LOOP;
>
> when is it more wise to run a commit on the data?
> - after each update?
> - after all the elements are looped through?
> If I have a lot of data that is suppose to be updated, and i have (>=
> 300000)
> is it better to do a commit for each element?
>
> thanks.-
> ranli..... :)

The first rule is :
if you can do it non procedurally, using SQL, you should do it using SQL, as that will be the most efficient solution. The procedure you describe above stinks heavily of being inefficient and not scalable, as for each update to be performed a context switch from PL/SQL to SQL and vice versa is required. The second rule is:
never ever split up a *logical* transaction into several *physical* transactions. If your physical transaction fails, how are you going to redo your logical transaction? How are you keeping track of what has been done and what has not been done? Probably you are not keeping track of that at all, and you are potentially ending up in an inconsistent situation.
Also you are going to be a likely victim of a self-inflicted ora-1555 error, snapshot too old.
If really someone was too stupid to buy sufficient disks, make sure you split up your logical transaction in distinct logical ranges, instead of trying to introduce hobby methods into enterprise software.

-- 
Sybrand Bakker
Senior Oracle DBA