Cursor Loop Updates

I’m writing this topic mostly for database developer coming from the programming world.

I have seen various procedural units which use the for syntax to run updates on base tables. Scenarios like the one bellow.

FOR update SQL:

for i in (select * from t1) loop
 update t1
 set amount=amount*(1+(Select adjustment from t2 where discount=i.discount 
    and categ=i.categ))
 where row_id=i.row_id;
end loop;
commit;

The scenario above is the basic example where one table is updated using values from a second table.

Now, in order to understand the basic problem with the above example there is something else you need to keep in mind. In Oracle, the PL/SQL and the SQL are 2 very specific modules. Whenever you call a SQL statement in a PL/SQL block, you are doing a context switch. While this can be very easy on a simple statement, larger data volumes updates like the one above will show you the cost of that context switch.

The scope of this post is to create a thinking methodology shift into a database oriented one, explaining the pro an cons of this approach, together with options of conversion for this type of code into classical DML statements.

Basically, what I’m trying to suggest is use PL/SQL only when procedural thinking is required, but do use your SQL in the most efficient manner to avoid these costly mistakes. Note that 70-80% of these type of syntax use cases can be rewritten in plain SQL.

For instance, our prior for loop can be rewritten into :

merge into t1
 using t2
on ( t1.discount=t2.discount and t1.categ=t2.categ)
when matched then 
 update
 set amount=amount*(1+t2.adjustment);
commit;

Now,

The first example, using the cursor based update, in my demo VM test environment run for about 15 minutes to update a table of 100K rows using a mapping table of 19 rows

while the second occurrence took only  less than 2 seconds.

Now please note this is a basic example, but I have not, as of now, met a situation where this was not the case.

Please note merge will not allow you to update the match condition key fields, therefore some situations might require you to adjust your syntax accordingly.

Scripts:

test-scripts

Please note scripts are uploaded as PDFs but you can still copy the code in.

Note: this was run on Oracle Database 12C 12.1.0.1.0