1 2 Previous Next 18 Replies Latest reply: Nov 5, 2012 7:03 AM by Billy~Verreynne RSS

    Two DML operations in one FORALL?

    a.stoyanov
      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
          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
            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
              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
                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
                  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
                    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
                      >
                      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?
                        Billy~Verreynne
                        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?
                          Billy~Verreynne
                          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?
                            Billy~Verreynne
                            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
                              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
                                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?
                                  Billy~Verreynne
                                  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?
                                    Billy~Verreynne
                                    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