Jasper Jasper - 1 month ago 7
SQL Question

Updating Table Records in a Batch and Auditing it

Consider this Table:

Table: ORDER
Columns: id, order_num, order_date, order_status

This table has 1 million records. I want to update the order_status to value of '5', for a bunch (about 10,000) of order_num's that i will be reading from a input text file.

My SQL could be:
(A) update ORDER set order_status=5 where order_num in ('34343', '34454', '454545',...)
OR
(B) update ORDER set order_status=5 where order_num='34343'


I can loop over this update several times until I have covered my 10,000 order updates.
(Also note that i have few Child Tables of ORDER like ORDER_ITEMS, where similar status must be updated and information audited)

My problem is here is:

How can i Audit this update in a separate ORDER_AUDIT Table:

Order_Num: 34343 - Updated Successfully

Order_Num: 34454 - Order Not Found

Order_Num: 454545 - Updated Successfully

Order_Num: 45457 - Order Not Found


If i go for batch update as in (A), I cannot Audit at Order Level.

If i go for Single Order at at time update as in (B), I will have to loop 10,000 times - that may be quite slow - but I can Audit at Order level in this case.

Is there any other way?

APC APC
Answer

First of all, build an external table over your "input text file". That way you can run a simple single UPDATE statement:

update ORDER 
set order_status=5 
where order_num in ( select col1 from ext_table order by col1)

Neat and efficient. (Sorting the sub-query is optional: it may improve the performance of the update but the key point is, we can treat external tables like regular tables and use the full panoply of the SELECT syntax on them.) Find out more.

Secondly use the RETURNING clause to capture the hits.

update ORDER 
set order_status=5 
where order_num in ( select col1 from ext_table order by col1)
returning order_num bulk collect into l_nums;

l_nums in this context is a PL/SQL collection of type number. The RETURNING clause will give you all the ORDER_NUM values for updated rows only. Find out more.

If you declare the type for l_nums as a SQL nested table object you can use it in further SQL statements for your auditing:

 insert into order_audit 
 select 'Order_Num: '||to_char(t.column_value)||' - Updated Succesfully'    
 from table ( l_nums ) t
 /

 insert into order_audit 
 select 'Order_Num: '||to_char(col1)||' - Order Not Found'    
 from ext_table 
 minus 
 select * from table ( l_nums ) 
 /

Notes on performance:

You don't say how many of the rows you have in the input text file will match. Perhaps you don't know (actually on re-reading it's not clear whether 10,000 is the number of rows in the file or the number of matching rows). Pl/SQL collections use private session memory, so very large collections can blow the PGA. However, you should be able to cope with ten thousand NUMBER instances without blinching.

My solution does require you to read the external table twice. This shouldn't be a problem. And it will certainly be way faster than dynamically assembling one hundred IN clauses of a thousand numbers and looping over each.

Note that update is often the slowest bulk operation known to man. There are ways of speeding them up, but those methods can get quite involved. However, if this is something you'll want to do often and performance becomes a sticking point you should read this OraFAQ article.