Deferred update in Oracle PL / SQL

Question

I have a simple PL / SQL procedure, with one cursor, and I iterate over it. At each iteration, I make an UPDATE statement (after doing some business logic for the data).

However, if there are many iterations (tens of thousands), this can become rather slow because there is one UPDATE statement at each iteration.

Is there a way to somehow “delay” these updates so that they are executed immediately (and therefore much faster).

Edit: Oracle 11

Answer 1

If you cannot figure out how to use direct SQL (avoiding the for loop all together), you can probably improve performance using the BULK Collection pl / sql functions.

Sample article here.

Syntax excerpt

LOOP FETCH c_orders BULK COLLECT INTO v_order_ids, v_currency_codes, v_amounts_local LIMIT 100; EXIT WHEN v_row_count = c_orders%ROWCOUNT; v_row_count := c_orders%ROWCOUNT; FOR i IN 1..v_order_ids.count LOOP v_amounts_usd(i) := currency_convert (v_amounts_local(i), v_currency_codes(i)); END LOOP; FORALL i IN 1..v_order_ids.count UPDATE open_orders /* bulk bind */ SET amount_usd = v_amounts_usd(i) WHERE order_id = v_order_ids(i); END LOOP; 

Answer 2

I do not see any analysis of the problem, but I see that people make many assumptions and draw conclusions. To get a more systematic approach, I recommend the following:

• Enable trace level 10046 for the session when starting the update job to see how much time is spent most of the time.
• Get session level statistics before and after the update job starts.

See where most of the time is spent and solves a real problem.

Answer 3

Try, if at all possible, to follow the business logic in the update statement. It will be much faster.