This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Nov 5, 2012 5:03 AM by BillyVerreynne RSS

Two DML operations in one FORALL?

a.stoyanov Newbie
Currently Being Moderated
Hi all,
In 11g is it possible to make two dml operations in one Forall loop?

For example:
SQL> create table xx_test (col1 number, col2 number, col3 number);
 
Table created
 
SQL> create table xx_test1 (col1 number, col2 number, col3 number);
 
Table created

SQL> insert into xx_test values(1,2,3);
 
1 row inserted
 
SQL>  insert into xx_test values(1,2,3);
 
1 row inserted
 
SQL>  insert into xx_test values(4,5,6);
 
1 row inserted
 
SQL>  insert into xx_test1 values(6,7,8);
 
1 row inserted

SQL> declare 
  2  cursor c is select col1, col2, col3 from xx_test;
  3  type t is table of c%rowtype;
  4  v t;
  5  begin 
  6   open c;
  7  loop
  8  fetch c bulk collect into v limit 1000;
  9   forall i in 1..v.count
 10   update xx_test1
 11  set col1 = v(i).col2;
 12  
 13  insert into xx_test1(col1,col2,col3) values(v(i).col1,v(i).col2,v(i).col3);
 14  
 15  exit when c%notfound;
 16  end loop;
 17  
 18  end;


ORA-06550: line 14, column 50:
PLS-00201: identifier 'I' must be declared
ORA-06550: line 14, column 50:
PLS-00201: identifier 'I' must be declared
ORA-06550: line 14, column 48:
PL/SQL: ORA-00904: : invalid identifier
ORA-06550: line 14, column 4:
PL/SQL: SQL Statement ignored
any ideas? I know that this can be achieved by processing row by row but in my case the cursor retrieves a lot of rows...


Thanks in advance,
Alexander.
  • 1. Re: Two DML operations in one FORALL?
    sb92075 Guru
    Currently Being Moderated
    update xx_test1 set col1 = v(i).col2;
    above changes EVERY row in the table.
    Do you really want to do this?
  • 2. Re: Two DML operations in one FORALL?
    a.stoyanov Newbie
    Currently Being Moderated
    hi,
    10x for reply.. it's a test... it doesn't matter what will be happen with the data of these tables... the issue is different from the logic based in this example


    Thanks,
    Alexander.
  • 3. Re: Two DML operations in one FORALL?
    thomaso Journeyer
    Currently Being Moderated
    No, you cannot.
    The keyword FORALL lets you run multiple DML statements very efficiently. It can only repeat a single DML statement, unlike a general-purpose FOR loop. For full syntax and restrictions, see "FORALL Statement".
    from
    http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/tuning.htm#i48876

    HTH
    Thomas
  • 4. Re: Two DML operations in one FORALL?
    lee200 Pro
    Currently Being Moderated
    As Thomas says you can't, but you could just put another FORALL statement before your INSERT
  • 5. Re: Two DML operations in one FORALL?
    padders Pro
    Currently Being Moderated
    It's likely syntactically possible but it is probably not a good idea. You might be able to implement something with FORALL MERGE for or FORALL EXECUTE IMMEDIATE but you would be making the approach overly complex and very possibly throwing away any performance gains.

    What is wrong with two FORALL statements in a row?
  • 6. Re: Two DML operations in one FORALL?
    AlbertoFaenza Expert
    Currently Being Moderated
    Hi,

    consider also the possibility of using MERGE without any FORALL statements.

    If you can do it with SQL then this should be the preferred solution.

    Regards.
    Al
  • 7. Re: Two DML operations in one FORALL?
    rp0428 Guru
    Currently Being Moderated
    >
    It's likely syntactically possible but it is probably not a good idea.
    >
    No - it isn't. A FORALL can only include one sql statement as the syntax diagram shows.
    http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/forall_statement.htm#i34324
  • 8. Re: Two DML operations in one FORALL?
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    a.stoyanov wrote:

    In 11g is it possible to make two dml operations in one Forall loop?
    Yes. But dynamic SQL is needed.

    Example:
    SQL> create table tab1( id integer, name varchar2(10) );
    
    Table created.
    
    SQL> create table tab2( id integer, job varchar2(10) );
    
    Table created.
    
    SQL> 
    SQL> 
    SQL> declare
      2          cursor c is
      3          select empno, ename, job from emp;
      4  
      5          type TBuffer is table of c%RowType;
      6          buffer  TBuffer;
      7  begin
      8          open c;
      9          loop
     10                  fetch c bulk collect into buffer limit 100;
     11  
     12                  forall i in 1..buffer.Count
     13                          execute immediate
     14                          'begin
     15                                  insert into tab1 values( :1, :2 );
     16                                  insert into tab2 values( :1, :3 );
     17                          end;'
     18                          using buffer(i).empno , buffer(i).ename, buffer(i).job;
     19  
     20                  exit when c%NotFound;
     21          end loop;
     22          close c;
     23  end;
     24  /
    
    PL/SQL procedure successfully completed.
    
    SQL> 
    SQL> select * from tab1;
    
            ID NAME
    ---------- ------------------------------
          7369 SMITH
          7499 ALLEN
          7521 WARD
          7566 JONES
          7654 MARTIN
          7698 BLAKE
          7782 CLARK
          7788 SCOTT
          7839 KING
          7844 TURNER
          7876 ADAMS
          7900 JAMES
          7902 FORD
          7934 MILLER
    
    14 rows selected.
    
    SQL> select * from tab2;
    
            ID JOB
    ---------- ----------
          7369 CLERK
          7499 SALESMAN
          7521 SALESMAN
          7566 MANAGER
          7654 SALESMAN
          7698 MANAGER
          7782 MANAGER
          7788 ANALYST
          7839 PRESIDENT
          7844 SALESMAN
          7876 CLERK
          7900 CLERK
          7902 ANALYST
          7934 CLERK
    
    14 rows selected.
    
    SQL> 
  • 9. Re: Two DML operations in one FORALL?
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    padders wrote:
    It's likely syntactically possible but it is probably not a good idea. You might be able to implement something with FORALL MERGE for or FORALL EXECUTE IMMEDIATE but you would be making the approach overly complex and very possibly throwing away any performance gains.

    What is wrong with two FORALL statements in a row?
    The problem is shipping off the same bind array twice to the SQL engine.

    Granted, perhaps a very minor overhead in PL/SQL. However, the same approach is valid for array binding from an external client like a C/C++ process. You would want to send a bind array once across the network to the SQL engine for processing - and not twice.

    So I would not call this approach, even in PL/SQL, wrong. It can be very useful.
  • 10. Re: Two DML operations in one FORALL?
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    rp0428 wrote:
    >
    It's likely syntactically possible but it is probably not a good idea.
    >
    No - it isn't. A FORALL can only include one sql statement as the syntax diagram shows.
    http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/forall_statement.htm#i34324
    Padders is right - as he usually is. (do not mistake his 80 postings for a new guy, his old OTN account was trashed - he was a regular many years ago and always offered good advice) :-)
  • 11. Re: Two DML operations in one FORALL?
    rp0428 Guru
    Currently Being Moderated
    Well I think I need to run a trace to try to see what Oracle is doing with this. If you adapt your code to OPs requirements this is what you get
     declare 
      cursor c is select col1, col2, col3 from xx_test;
      type t is table of c%rowtype;
      v t;
      begin 
       open c;
      loop
      fetch c bulk collect into v limit 1000;
       forall i in 1..v.count
          execute immediate
          'begin
              update xx_test1 set col1 = :1;
              insert into xx_test1(col1,col2,col3) values(:2, :3, :4);
             end;'
             using v(i).col2, v(i).col1, v(i).col2, v(i).col3;
      exit when c%notfound;
      end loop;
      end;
    /
    If you examine the output you see that there is definitely a loop of three iterations. Each iterations does an UPDATE and then an INSERT.
    #1 - updates the 1 record in test1 from 6,7,8 to 2,7,8 and inserts a record for 1,2,3
    #2 - updates both records in test1 - 2,7,8 to 2,7,8 (no change) 1,2,3 to 2,2,3 and inserts a record for 1,2,3
    #3 - updates all three records in test1 - 2,7,8 to 5,7,8 and 2,2,3 to 5,2,3 and 1,2,3 to 5,2,3 and inserts a record for 4,5,6

    SQL> select * from xx_test1;

    COL1 COL2 COL3
    ---------- ---------- ----------
    5 7 8
    5 2 3
    5 2 3
    4 5 6

    As far as I can tell the UPDATEs and INSERTs are interleaved; that is the UPDATE updates a different number of records each time.

    So if the bind array was shipped of to the SQL engine the sql engine is looping over both statements. I'm curious to see if a trace shows that happening or if it is the PL/SQL engine doing the looping and not using the FORALL functionality.
  • 12. Re: Two DML operations in one FORALL?
    Stew Ashton Expert
    Currently Being Moderated
    rp0428 wrote:
    So if the bind array was shipped of to the SQL engine the sql engine is looping over both statements.
    This is in reply to Billy and rp0428.

    The idea behind FORALL is to ship an array of bind variables to the SQL engine and have the actual statement executed once.

    If you take Billy's example above, which does two inserts within an anonymous block, the insert statements are executed multiple times.

    Let's run it:
    drop table tab1;
    CREATE TABLE TAB1( ID INTEGER, NAME VARCHAR2(10) );
    drop table tab2;
    create table tab2( id integer, job varchar2(10) );
    
    alter system flush shared_pool;
    
    declare
      CURSOR C IS
      SELECT EMPNO, ENAME, JOB FROM scott.EMP;
      type TBuffer is table of c%RowType;
      BUFFER  TBUFFER;
    begin
      open c;
      loop
        fetch c bulk collect into buffer limit 100;
        
        forall i in 1..buffer.Count
          execute immediate
            'begin
              insert into tab1 values( :1, :2 );
              insert into tab2 values( :1, :3 );
            end;'
          using buffer(i).empno , buffer(i).ename, buffer(i).job;
        
        exit when c%NotFound;
      end loop;
      CLOSE C;
    end;
    / 
    select executions from v$sql where sql_text like 'INSERT INTO TAB%';
    
    EXECUTIONS
    ----------
            14 
            14
    This matches with rp0428's observations from his test: his updates and inserts are executed one by one and not in bulk.

    Now let's do a FORALL with static SQL, again using Billy's example but doing the job with one INSERT ALL:
    alter system flush shared_pool;
    
    declare
      CURSOR C IS
      SELECT EMPNO, ENAME, JOB FROM scott.EMP;
      type TBuffer is table of c%RowType;
      BUFFER  TBUFFER;
    begin
      open c;
      loop
        fetch c bulk collect into buffer limit 100;
        
        FORALL I IN 1..BUFFER.COUNT
              INSERT ALL
              INTO TAB1 VALUES(BUFFER(I).EMPNO , BUFFER(I).ENAME)
              INTO TAB1 VALUES(BUFFER(I).EMPNO , BUFFER(I).JOB)
              select null from dual;
        
        exit when c%NotFound;
      end loop;
      CLOSE C;
    end;
    / 
    select executions from v$sql where sql_text like 'INSERT ALL%';
    
    EXECUTIONS
    ----------
             1
    Bottom line: the point of FORALL is to reduce "context switches" between PL/SQL and SQL, and to reduce the number of executions of the SQL statement. Doing a FORALL with an anonymous PL/SQL block provides neither of those benefits.

    Edited by: Stew Ashton on Nov 3, 2012 11:20 AM
  • 13. Re: Two DML operations in one FORALL?
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Stew, a bulk bind from an OCI client looks as follows:
    OCIStmtPrepare()
    OCIBindByName()/OCIBindByPos() (binding a host array)
    while some-condition loop
      fill host array
      OCIStmtExecute()  (specify the number of elements in array via the iters param)
    end loop
    The statement is executed once for each array element - according to the OCI guide. So in the case of sending a 100 elements, the statement will be executed (iterated) by Oracle, a 100 times. It however is not exactly clear on how the server-side deals with this execution.

    The issue you raised with cursor execution counts, seems to be whether the statement is single statement, or single statement with nested statements.

    I have written an OCI client doing the exact same tests as were done in PL/SQL using FORALL in this thread.

    If the SQL statement executed is SQL, the cursor that is created is executed only "once" (not exactly true as multiple rows are inserted using a single row DML).

    If the statement is PL/SQL, the cursor that is created, is executed "once". So pretty much the same behaviour. However, as this statement contains "nested" SQLs (the actual DML statements), these also need to be parsed and executed as cursors. In which case you see these as being executed a 100 times (once per element in for the bind array).

    The issue is whether or not the FORALL DML statement is executed once (as it would appear from the executions column), or not?

    It would seem that there is some funky happening (some kind of call optimisation perhaps?) when Oracle deals with an array bind - as the cursor seems to be executed once. But that in fact is not the case as that cursor only inserts a single row. And multiple rows are inserted.

    E.g. simplistic example to see how many times the FORALL DML statement is executed:
    SQL> create sequence emp_id_seq
      2          start with 1
      3          increment by 1
      4          nocycle
      5          nomaxvalue;
    
    Sequence created.
    
    SQL> --// add a PL/SQL user function wrapper for the sequence
    SQL> create or replace function GetNextEmpID return number is
      2          id      number;
      3  begin
      4          select emp_id_seq.NextVal into id from dual;  --// explicit SQL statement
      5          return( id );
      6  end;
      7  /
    
    Function created.
    
    SQL> 
    SQL> declare
      2          cursor c is
      3          select empno, ename, job from emp;
      4  
      5          type TBuffer is table of c%RowType;
      6          buffer  TBuffer;
      7  begin
      8          open c;
      9          loop
     10                  fetch c bulk collect into buffer limit 100;
     11  
     12                  forall i in 1..buffer.Count
     13                          insert into tab1 values( GetNextEmpID(), buffer(i).ename );
     14                  exit when c%NotFound;
     15          end loop;
     16          close c;
     17  end;
     18  /
    
    PL/SQL procedure successfully completed.
    
    SQL> 
    SQL> select
      2          executions,
      3          sql_text
      4  from       v$sql
      5  where      sql_text like 'INSERT INTO TAB1%'
      6  or sql_text like 'SELECT EMP_ID_SEQ%';
    
    EXECUTIONS SQL_TEXT
    ---------- --------------------------------------------------
            14 SELECT EMP_ID_SEQ.NEXTVAL FROM DUAL
             1 INSERT INTO TAB1 VALUES( GETNEXTEMPID(), :B1
    So the insert seems to have been executed once. However, the wrapper was called 14 times and its SQL statement was called 14 times. Once per bind array value.

    So there do seem to be some kind of optimisation on the Oracle side - however, it does not mean that the FORALL statement is not using bulk/array binding. It is. And that is what the FORALL statement is designed to do.
  • 14. Re: Two DML operations in one FORALL?
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    A simplistic guess as to what is happening.

    Some Oracle kernel module needs to be called to execute the actual insert cursor. It needs to have the data to insert.

    So if this module touches the execution count and is called with an array of values to process, it could be doing the following:

    E.g.
    void module exec-cursor( cursorHandle .., bindArea .. ) {
      cursorHandle.execution.Count++
      for i in 1..bindArea.count 
        do the execution cursorHandle for bindArea[i]
      end loop;
    }
    In this case the exec-cursor() module was called once, incremented the execution count by 1 - but internally it did several iterations.

    This could explain what we are seeing.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points