Question by Brannan: I note that the FORALL operator is
available for bulking of delete statements. How does the context
switch work with bulking of deletes? I understand why forall is
faster for inserts and updates, but with deletes there are no rows to
transfer during the context switch. Is there any performance
difference between these two deletes, one using forall?
FORALL
forall i in 1 .. l_c1.count
delete from .....;
FOR..LOOP
for i in 1 .. l_c1.count loop
delete from .....;
end loop;
Answer: I'm a DBA, not a coder, but lets see what we can find on
forall.
If you
don't already have it, get Dr. Hall's book "PL/SQL
Tuning Secrets", a great book for learning about PL/SQL bulking.
I would
guess that the forall for delete reduces context switches, but it would
not be as much as an insert. The docs suggest that forall is done
faster with deletes:
The
FORALL statement in Example 11-2 sends all three DELETE statements
to the SQL engine at once.
Example 11-2 Issuing DELETE Statements in a Loop
CREATE TABLE employees_temp AS
SELECT * FROM employees;
DECLARE
TYPE NumList IS VARRAY(20) OF NUMBER;
depts NumList := NumList(10, 30, 70); -- department numbers
BEGIN
FORALL i IN depts.FIRST..depts.LAST
DELETE FROM employees_temp WHERE department_id = depts(i);
COMMIT;
END;
/
See my
related note on PL/SQL bulk operator performance:
