Solved

replace a for loop with forall or bulk collect

Posted on 2004-10-08
14
2,899 Views
Last Modified: 2008-01-09
Hi,

How would I convert the below statement into a bulk collect/forall statement and would this improve performance?

----------------------------------------------
 CURSOR curQTYRequested IS
    SELECT  sum(tko.QTY_REQUESTED) TOTALQTY, tko.POINT_OF_MF, tko.YEAR, tko.WEEK_NUMBER, tko.FINISHED_TYPE
      FROM OTIF_EVE tko
      WHERE upper(tko.EXCLUDE) = 'N'
      AND   ((DECODE(Upper(substr(tko.REASON_CODE,1)),NULL,'A',Upper(substr(tko.REASON_CODE,0,1))) <> 'C')AND(DECODE(Upper(substr(tko.REASON_CODE,1)),NULL,'A',Upper(substr(tko.REASON_CODE,0,3))) <> 'D02'))
      GROUP BY (tko.POINT_OF_MF, YEAR, tko.WEEK_NUMBER, tko.FINISHED_TYPE);

begin

    FOR recQty IN curQTYRequested LOOP

                   UPDATE OTIF_EVE_POM_TOTALS_W
                        SET    TOTAL_VOLUME_REQUESTED       =  recQty.TOTALQTY
                     WHERE   POM_ID               =  recQty.POINT_OF_MF
                        AND         WEEK_NUMBER    =  recQty.WEEK_NUMBER
                     and          YEAR                    =  recQty.YEAR
                     and     FINISHED_TYPE =  recQty.FINISHED_TYPE;

                  if SQL%NOTFOUND then
                  
                   INSERT INTO OTIF_EVE_POM_TOTALS_W
                                          (POM_ID,
                                            WEEK_NUMBER,
                                          YEAR,
                                          TOTAL_VOLUME_REQUESTED,
                                          FINISHED_TYPE)
                           VALUES              (recQty.POINT_OF_MF,
                                                       recQty.Week_number,
                                                   recQty.Year,
                                                   recQty.TOTALQTY,
                                                   recQty.FINISHED_TYPE
                                                     );
                end if;

  END LOOP;

---------------------------------------------

thanks
0
Comment
Question by:joehodge
  • 8
  • 3
  • 2
  • +1
14 Comments
 
LVL 23

Expert Comment

by:seazodiac
ID: 12257790
try:

begin

   FORALL recQty IN curQTYRequested LOOP

                UPDATE OTIF_EVE_POM_TOTALS_W
                    SET    TOTAL_VOLUME_REQUESTED       =  recQty.TOTALQTY
                 WHERE   POM_ID             =  recQty.POINT_OF_MF
                    AND        WEEK_NUMBER    =  recQty.WEEK_NUMBER
                 and         YEAR                 =  recQty.YEAR
                 and     FINISHED_TYPE =  recQty.FINISHED_TYPE;

              if SQL%NOTFOUND then
             
                 INSERT INTO OTIF_EVE_POM_TOTALS_W
                                    (POM_ID,
                                    WEEK_NUMBER,
                                  YEAR,
                                  TOTAL_VOLUME_REQUESTED,
                                  FINISHED_TYPE)
                       VALUES            (recQty.POINT_OF_MF,
                                              recQty.Week_number,
                                          recQty.Year,
                                          recQty.TOTALQTY,
                                          recQty.FINISHED_TYPE
                                            );
             end if;

 END LOOP;
0
 

Author Comment

by:joehodge
ID: 12257820
will that have an impact on performance?
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 12257823
yes, improving...reduce context switch between plsql and sql engine...
0
 

Author Comment

by:joehodge
ID: 12257828
I replaced the 'for' with 'forall' and got:

PLS-00103: Encountered the symbol "LOOP" when expecting one of the following:

   . ( * @ % & - + / at mod rem .. <an exponent (**)> ||
0
 
LVL 23

Accepted Solution

by:
seazodiac earned 125 total points
ID: 12257849
sorry, the syntax has failed me again...

here is what you need to do:

you have declare an collect type for each columns in your select cursor,

then

do a bulk collect to fetch the cursor result to these collect table type fro each column.

then loop through collect to do the BULK binding

I am off to work, will write the code later...but the logic is the same....


for reference, take a look at:

http://www.databasejournal.com/features/oracle/article.php/3383061


it would be a nice practice for you to figure it out on your own

0
 

Author Comment

by:joehodge
ID: 12257922
cheers seazodiac,

as I'm a one man band I've got to do the complete project lifecycle myself. So, whilst I'm doing some mundane testing/documentation I'll think about how I can improve my code, fire off a question, get an answer and then go and rip aprt my poorly written code!

I'll see if I can write the code and will try and post it before you do.
0
 
LVL 13

Expert Comment

by:riazpk
ID: 12258091
So why don't you use the MERGE command (if you have Oracle 9i) ?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 7

Expert Comment

by:Bigfam5
ID: 12258455
Why have a LOOP if you are going to update or insert those that do not exist.  You could simply have an update and insert

Your Update could be something like

Update OTIF_EVE_POM_TOTALS_W A
          set (TOTAL_VOLUME_REQUESTED, POM_ID, WEEK_NUMBER, YEAR, FINISHED_TYPE) =
     ( SELECT sum(tko.QTY_REQUESTED) TOTALQTY, tko.POINT_OF_MF, tko.YEAR,
              tko.WEEK_NUMBER, tko.FINISHED_TYPE
         FROM OTIF_EVE tko
         WHERE upper(tko.EXCLUDE) = 'N'
          AND (   (DECODE(Upper(substr(tko.REASON_CODE,1)),NULL,'A',Upper(substr(tko.REASON_CODE,0,1))) <> 'C')
              AND (DECODE(Upper(substr(tko.REASON_CODE,1)),NULL,'A',Upper(substr(tko.REASON_CODE,0,3))) <> 'D02') ) )
          AND (WHERE TKO.POM_ID = A.POINT_OF_MF
                 AND TKO.WEEK_NUMBER = A.WEEK_NUMBER
                 and TKO.YEAR = A.YEAR
                 and TKO.FINISHED_TYPE = A.FINISHED_TYPE)
      GROUP BY (tko.POINT_OF_MF, YEAR, tko.WEEK_NUMBER, tko.FINISHED_TYPE)
 WHERE EXISTS (SELECT 1 FROM OTIF_EVE tko
                 WHERE upper(tko.EXCLUDE) = 'N'
                   AND (   (DECODE(Upper(substr(tko.REASON_CODE,1)),NULL,'A',Upper(substr(tko.REASON_CODE,0,1))) <> 'C')
                       AND (DECODE(Upper(substr(tko.REASON_CODE,1)),NULL,'A',Upper(substr(tko.REASON_CODE,0,3))) <> 'D02') ) )
                   AND (WHERE TKO.POM_ID = A.POINT_OF_MF
                   AND TKO.WEEK_NUMBER = A.WEEK_NUMBER
                   and TKO.YEAR = A.YEAR
                   and TKO.FINISHED_TYPE = A.FINISHED_TYPE)
/


and the insert

INSERT INTO OTIF_EVE_POM_TOTALS_W
           (POM_ID,  WEEK_NUMBER,     YEAR,
            TOTAL_VOLUME_REQUESTED,  FINISHED_TYPE)
( SELECT tko.POINT_OF_MF, tko.WEEK_NUMBER, tko.YEAR,
         sum(tko.QTY_REQUESTED) TOTALQTY, tko.FINISHED_TYPE
     FROM OTIF_EVE tko
     WHERE upper(tko.EXCLUDE) = 'N'
       AND (   (DECODE(Upper(substr(tko.REASON_CODE,1)),NULL,'A',Upper(substr(tko.REASON_CODE,0,1))) <> 'C')
           AND (DECODE(Upper(substr(tko.REASON_CODE,1)),NULL,'A',Upper(substr(tko.REASON_CODE,0,3))) <> 'D02') ) )
   GROUP BY (tko.POINT_OF_MF, YEAR, tko.WEEK_NUMBER, tko.FINISHED_TYPE)
 WHERE not EXISTS (SELECT 1 FROM OTIF_EVE_POM_TOTALS_W A
                     WHERE TKO.POM_ID = A.POINT_OF_MF
                       AND TKO.WEEK_NUMBER = A.WEEK_NUMBER
                       and TKO.YEAR = A.YEAR
                       and TKO.FINISHED_TYPE = A.FINISHED_TYPE)


Really no need to have a loop and this is the fastest (performance wise) approach


0
 

Author Comment

by:joehodge
ID: 12258486
Hi,

type numcol is table of NUMBER index by binary_integer;
  lv_SUM     numcol;
  lv_year        numcol;
  lv_week_number numcol;
 
type varcol is table of VARCHAR(50) index by binary_integer;  
  lv_region varcol;
  lv_finished_type varcol;
 
BEGIN

 EXECUTE IMMEDIATE 'TRUNCATE TABLE OTIF_OWNER.OTIF_EVE_TOTALS_WR';
 COMMIT;

    SELECT sum(tko.QTY_REQUESTED) TOTALQTY, REGION_DESCRIPTION, tko.YEAR, tko.WEEK_NUMBER, tko.FINISHED_TYPE bulk collect
      INTO   lv_SUM, lv_region, lv_year, lv_week_number,lv_finished_type
      FROM   OTIF_EVE tko
      WHERE  upper(tko.EXCLUDE) = 'N'
      GROUP BY (REGION_DESCRIPTION, YEAR, tko.WEEK_NUMBER, tko.FINISHED_TYPE);


forall i in lv_region.first .. lv_region.last
   
          INSERT INTO OTIF_EVE_TOTALS_WR
                                          (REGION_DESCRIPTION,      WEEK_NUMBER, YEAR, TOTAL_VOLUME_REQUESTED,      FINISHED_TYPE)
        VALUES                       (lv_region(i), lv_week_number(i), lv_year(i),lv_SUM(i), lv_finished_type(i));
---------------------------------------------------

this works fine but how do I find out the performance improvement?

I put

lv_timestamp :=dbms_utility.get_time;
before and then
lv_timestamp2 := dbms_utility.get_time - lv_timestamp;
htp.p(lv_timestamp);

for the original insert the results were between 19-22

for the forall the results were 18-23

I've only got 1 weeks worh of data in the table. will the improvements only show when there is a lot of data being processed or is this not the best way to show the performance?

p.s. I was told that its better to truncate rather than delete but with a delete from OTIF_EVE_TOTALS_WR the time goes down to 3-7??
whats all that about?
0
 

Author Comment

by:joehodge
ID: 12258563
whats the merge command?
0
 

Author Comment

by:joehodge
ID: 12258808
Hi BigFam5,

I tried the sql

 EXECUTE IMMEDIATE 'TRUNCATE TABLE OTIF_OWNER.OTIF_EVE_POM_TOTALS_W';
 commit;

   INSERT INTO OTIF_EVE_POM_TOTALS_W
           (POM_ID,  WEEK_NUMBER,     YEAR, TOTAL_VOLUME_REQUESTED,  FINISHED_TYPE)
                                  (SELECT tko.POINT_OF_MF, tko.WEEK_NUMBER, tko.YEAR,sum(tko.QTY_REQUESTED) TOTALQTY, tko.FINISHED_TYPE
                             FROM OTIF_EVE tko
                               WHERE upper(tko.EXCLUDE) = 'N'
                                 AND ((DECODE(Upper(substr(tko.REASON_CODE,1)),NULL,'A',Upper(substr(tko.REASON_CODE,0,1))) <> 'C')
                          AND (DECODE(Upper(substr(tko.REASON_CODE,1)),NULL,'A',Upper(substr(tko.REASON_CODE,0,3))) <> 'D02'))
                                   GROUP BY (tko.POINT_OF_MF, YEAR, tko.WEEK_NUMBER, tko.FINISHED_TYPE));
 
and the time was 32-33 so I'm afraid I cant agree that it is the quickest.

I left off the where exists as i truncated the table, would this improve its performance?
0
 

Author Comment

by:joehodge
ID: 12259172
Hi,

when I make my second statement I tried passing in lv_sum, lv_region etc but no update would occur.

How can I clear the values in the lv_sum, lv_region instead of doing what I'm doing below?

SELECT   count(*) NUM_LINES_OTIF, REGION_DESCRIPTION, tko.YEAR, tko.WEEK_NUMBER, tko.FINISHED_TYPE bulk collect
      INTO     lv_SUM_b, lv_region_b, lv_year_b, lv_week_number_b,lv_finished_type_b
      FROM     OTIF_EVE tko
      WHERE    upper(tko.EXCLUDE) = 'N'
      GROUP BY (REGION_DESCRIPTION, year, tko.WEEK_NUMBER, tko.FINISHED_TYPE);

    forall i in lv_region.first .. lv_region.last

                   UPDATE OTIF_EVE_TOTALS_WR
                        SET     LINES_REQUESTED     =  lv_SUM_b(i)
                     WHERE   REGION_DESCRIPTION  =  lv_region_b(i)
                        AND         WEEK_NUMBER             =  lv_week_number_b(i)
                     AND          YEAR                             =  lv_year_b(i)
                     AND          FINISHED_TYPE           =  lv_finished_type_b(i);
    commit;
0
 

Author Comment

by:joehodge
ID: 12259397
this does the job

type varcol is table of VARCHAR(50) index by binary_integer;  
  lv_region        varcol;
  lv_finished_type varcol;
 
  lv_region_null        varcol;
  lv_finished_type_null varcol;
 
  procedure pr_nullarrays is
  begin
   lv_region := lv_region_null;
   lv_SUM        := lv_SUM_null;
   lv_year        := lv_year_null;
   lv_week_number := lv_week_number;
   lv_finished_type := lv_finished_type_null;
   commit;
  end;

calling pr_nullarrays after the forall
0
 
LVL 13

Expert Comment

by:riazpk
ID: 13252886
Merge is basically an upsert (Update if exists , insert if doesn't exists). Here is a counter example:

SQL> ed
Wrote file afiedt.buf

  1* select * from emp
SQL>
SQL> /

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800     20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975     20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850         12         30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450     10
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000     20
      7839 KING       PRESIDENT            17-NOV-81       5000     10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 12-JAN-83       1100     20
      7900 JAMES      CLERK           7698 03-DEC-81        950     30
      7902 FORD       ANALYST         7566 03-DEC-81       3000     20
      7934 MILLER     CLERK           7782 23-JAN-82       1300     10

14 rows selected.

SQL> create table emp1 as select empno, sal*dbms_random.value()
  2  from emp;
create table emp1 as select empno, sal*dbms_random.value()
        *
ERROR at line 1:
ORA-00998: must name this expression with a column alias


SQL> ed
Wrote file afiedt.buf

  1  create table emp1 as select empno, sal*dbms_random.value() new_Sal
  2* from emp
SQL> /

Table created.

SQL> select * from emp1;

     EMPNO    NEW_SAL    
---------- ----------    
      7369 792.260813    
      7499  660.26187    
      7521 1026.96225    
      7566 1919.24537    
      7654  1247.3004    
      7698 210.936618    
      7782 2329.22473    
      7788  2812.2997    
      7839 2253.48061    
      7844 845.596914    
      7876 439.030483    
      7900 357.984645    
      7902 994.847609    
      7934 187.267564    

14 rows selected.

SQL> delete emp where empno=7934;

1 row deleted.

SQL> commit;

Commit complete.

SQL> merge into emp
  2  using emp1
  3  on (emp.empno=emp1.empno)
  4  when matched then
  5  update set sal=new_sal
  6  when not matched then
  7  insert (empno, sal)
  8  values(emp1.empno,new_sal)
  9  /

14 rows merged.

SQL> commit;

Commit complete.

SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80     792.26     20
      7499 ALLEN      SALESMAN        7698 20-FEB-81     660.26        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81    1026.96        500         30
      7566 JONES      MANAGER         7839 02-APR-81    1919.25     20
      7654 MARTIN     SALESMAN        7698 28-SEP-81     1247.3       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81     210.94         12         30
      7782 CLARK      MANAGER         7839 09-JUN-81    2329.22     10
      7788 SCOTT      ANALYST         7566 09-DEC-82     2812.3     20
      7839 KING       PRESIDENT            17-NOV-81    2253.48     10
      7844 TURNER     SALESMAN        7698 08-SEP-81      845.6          0         30
      7876 ADAMS      CLERK           7788 12-JAN-83     439.03     20
      7900 JAMES      CLERK           7698 03-DEC-81     357.98     30
      7902 FORD       ANALYST         7566 03-DEC-81     994.85     20
      7934  187.27      

14 rows selected.

SQL> select * from emp1;

     EMPNO    NEW_SAL    
---------- ----------    
      7369 792.260813    
      7499  660.26187    
      7521 1026.96225    
      7566 1919.24537    
      7654  1247.3004    
      7698 210.936618    
      7782 2329.22473    
      7788  2812.2997    
      7839 2253.48061    
      7844 845.596914    
      7876 439.030483    
      7900 357.984645    
      7902 994.847609    
      7934 187.267564    

14 rows selected.

SQL> spo off


HTH
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now