Saturday, October 10, 2009

Modify All the Data Needed in One Statement

Modify All the Data Need in One Statement
========================================

When possible, use array processing. This means that an array of bind variable values
is passed to Oracle for repeated execution. This is appropriate for iterative processes in which multiple rows of a set are subject to the same operation.

For example:

BEGIN
FOR pos_rec IN (SELECT *
FROM order_positions
WHERE order_id = :id) LOOP
DELETE FROM order_positions
WHERE order_id = pos_rec.order_id AND
order_position = pos_rec.order_position;
END LOOP;
DELETE FROM orders
WHERE order_id = :id;
END;


Alternatively, you could define a cascading constraint on orders. In the previous
example, one SELECT and n DELETEs are executed. When a user issues the DELETE on
orders DELETE FROM orders WHERE order_id = :id, the database automatically
deletes the positions with a single DELETE statement.

Or

Can be use

Bulk collect, for all etc functions
see:

http://halimdba.blogspot.com/2009/09/how-to-increase-performance-of-delete.html

No comments: