Difference Between FOR and FORALL Update in Oracle with Examples?

The FORALL allows performing the DML operations on data in bulk. It is similar to that of FOR loop statement except in FOR loop things happen at the record level whereas in FORALL there is no LOOP concept. Instead, the entire data present in the given range is processed at the same time.

This is a very generic and interesting question in Interviews, Difference between FOR sn FORALL Statement in Oracle. FOR Loop is a common term in all programming languages. FOR does the record processing one by one in a loop. FORALL is Generally not a Loop, Instead, we can call it a Statement, It allows only to do the Bulk DML Operations and nothing else.

Let us get into this topic in brief.

Syntax of FOR vs FORALL :

FOR temp_variable in  first_element .. last_element LOOP
//Code
END LOOP;

FORALL temp_variable in  first_element .. last_element LOOP
  //Code 

Example :

The below example shows the significant differences between FOR and FORALL, What makes FORALL Unique.

DECLARE
  TYPE ITEM_TAB IS TABLE OF ITEMS%ROWTYPE;
  ITEMS_TAB ITEM_TAB := ITEM_TAB();
  START_TIME   NUMBER;
  END_TIME     NUMBER;
BEGIN
 
SELECT * BULK COLLECT INTO ITEMS_TAB FROM ITEMS;

START_TIME := DBMS_UTILITY.GET_TIME;
  FOR I IN ITEMS_TAB.FIRST .. ITEMS_TAB.LAST LOOP
    INSERT INTO deliveries_tb
    VALUES
      (ITEMS_TAB(I).item_no,
       ITEMS_TAB(I).item_name,
       ITEMS_TAB(I).item_date);
      --CONDTIONS
      --PRINTS
      --INNER LOOPS
      --DDL and DML
  END LOOP;
 END_TIME := DBMS_UTILITY.GET_TIME;
 
 DBMS_OUTPUT.PUT_LINE('FOR LOOP INSERT TIME :' || TO_CHAR(END_TIME - START_TIME));

  EXECUTE IMMEDIATE 'TRUNCATE TABLE deliveries_tb';
  START_TIME := DBMS_UTILITY.GET_TIME;
  FORALL I IN ITEMS_TAB.FIRST .. ITEMS_TAB.LAST
    INSERT INTO deliveries_tb VALUES
      (ITEMS_TAB(I).item_no,
       ITEMS_TAB(I).item_name,
       ITEMS_TAB(I).item_date);
      --OTHER THAN DML, NO OTHER OPERATIONS ALLOWED
  END_TIME := DBMS_UTILITY.GET_TIME;
 
  DBMS_OUTPUT.PUT_LINE('FORALL BULK LOOP INSERT TIME :' || TO_CHAR(END_TIME - START_TIME));
  COMMIT;
END;

FOR LOOP INSERT TIME :2622
FORALL BULK LOOP INSERT TIME :97

In the above example, we were trying to insert 10 Lakh records into a Table to find the performances, and we identified around 2800 % performance improvement when we go for FORALL.

Conclusion :

FORALL mainly created for Bulk Processing to Optimize the Performances. The iterations on FORALL are not the same as in FOR Loop, it processes the iterations at the same time, So the Number of Updates under in FOR Loops takes much more time than FORALL. Hence if you want to focus on Performances with Only DML Operations in a Loop, Then FORALL is the way to go.