When running a forall loop in PL/SQL, is it necessary to commit afterwards?

Question

Does a PL/SQL forall loop commit automatically at certain intervals, or do I need to commit after the loop?

Oracle 10g and 11g

FORALL i IN x.FIRST .. x.LAST
    delete from table where 1=1;

I've currently got a pl/sql script that does a bulk collect, and then runs 3 different forall loops that iterate over the collection. I am currently committing after each forall loop completes, with a commit statement in the script. Is this needed? Does it slow down execution, especially when the collection has several million entries?

Thanks

Answer 1

A FORALL statement is standard DML: it is just a batch of individual statements. You should therefore follow the standard rules for deciding if you need to commit: Only commit at the end of your transaction when you have achieved a consistent state, never before.

There is no reason to commit 3 times if you have 3 FORALL statements except when each statement taken individually is a single transaction.

In any case, if your job fails after the first FORALL statement, it will be a lot easier to restart if you haven't commited yet.

Answer 2

You have to explicitly commit after a FORALL. After all, you are performing high speed DML using FORALL, and as you (should) know, DML does not commit automatically.

Also, even hough FORALL iterates through all the rows of a collection, it is not a loop, it is a statement. It has neither a LOOP nor an END LOOP statement.