4

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

skaffman
  • 398,947
  • 96
  • 818
  • 769
kg.
  • 633
  • 1
  • 5
  • 17

2 Answers2

4

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.

Eddie Awad
  • 3,669
  • 1
  • 19
  • 17
  • 2
    While I agree with everything you say in your answer, I don't think it answers the question completely. It is not *necessary* to commit after a FORALL statement. You can commit later, and you shouldn't commit before the end of your transaction. – Vincent Malgrat Mar 10 '11 at 12:37
4

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.

Vincent Malgrat
  • 66,725
  • 9
  • 119
  • 171