Optimize stored procedure loop to improve performance

Question

Products table:

PID START_DATE  END_DATE    PRODUCT_SET_ID  CREATED_DATE    UPDATED_DATE    PRODUCT_NAME    COMPANY_NM  PRICE
    1   04/01/11    06/30/11    12              09/06/16        09/06/16        Apple           ABC         50
    2   04/01/10    06/30/10    12              09/06/16        09/06/16        Toothpaste      PQR         80
    3   07/01/11    09/30/11    12              09/06/16        09/06/16        Soap            DOVE        53
    4   04/01/12    06/30/12    12              09/06/16        09/06/16        TV              ONIDA       50000

Order table:

OID PID PRODUCT_ID  SEQ_ID  TYPE_ID CREATED_DATE    UPDATED_DATE    NUMBER_VALUE    TEXT_VALUE
11  1   1           1       1       09/06/16        09/06/16        5
12  1   2           1       6       09/06/16        09/06/16        50  
13  2   1           1       3       09/06/16        09/06/16        3
14  2   2           1       7       09/06/16        09/06/16        80  

VA table:

Product_ID  Product_Name
1           Apple
2           Orange
3           Toothpaste
4           Soap
5           TV
6           ABC
7           PQR
8           DOVE
9           ONIDA

VA_IN_TB

TB_NM       COL_NM     PRODUCT_ID   SEQ_ID
Product_TB  Apple      1           1
Product_TB  Orange     2           1
Product_TB  Toothpaste 3           1
Product_TB  Soap       4           1
Product_TB  TV         5           1
Product_TB  ABC        6           1
Product_TB  PQR        7           1
Product_TB  DOVE       8           1
Product_TB  ONIDA      9           1

I also added index to products table:

CREATE INDEX INDX_PID ON PRODUCT_TABLE(PID);

Stored Procedure to generate Order table:

REC_COUNT:= SELECT COUNT(*) FROM PRODUCT_TABLE;
Loop_CT :=( REC_COUNT/2000000) +1;

SELECT MIN(PID) INTO LOWER_LIMIT FROM PRODUCT_TABLE;
UPPER_LIMIT := LOWER_LIMIT +2000000;

FOR i in 1..LOOP_COUNT LOOP
Create Table Temp_1 Nologging as
SELECT  ORDER_SEQ.NEXTVAL OID,
        A.PID,A.PRODUCT_ID,
        A.SEQ_ID,
        A.VALUE,
        B.TYPE_ID 
FROM 
    (
    SELECT  A.PID,
            A.VA_ID,
            A.SEQ_ID,
            A.VALUE,
            B.TYPE_NAME 
    FROM
        (
        select  A.PID, 
                B.PRODUCT_ID,
                B.SEQ_ID, 
                PRODUCT_NAME VALUE 
        from PRODUCT_TABLE A 
        JOIN VA_IN_TB B 
            ON REPLACE(A.PRODUCT_NAME,'''','')=REPLACE(B.COL_NM,'''','') 
            AND A.PID BETWEEN LOWER_LIMIT AND UPPER_LIMIT
        union all
        select  A.PID,
                B.PRODUCT_ID,
                B.SEQ_ID, 
                PRICE VALUE 
        from PRODUCT_TABLE A 
        JOIN VA_IN_TB B 
            ON REPLACE(A.COMPANY_N,'''','')=REPLACE(B.COL_NM,'''','')
            AND A.PID BETWEEN LOWER_LIMIT AND UPPER_LIMIT
        ) A 
    LEFT JOIN VA_TB B 
        ON A.PRODUCT_ID=B.PRODUCT_ID
    ) A 
LEFT JOIN VA_TB B 
    ON A.TYPE_NAME=B.PRODUCT_NAME

Insert INTO ORDER_TB
SELECT * FROM TEMP_1;
Commit;

DROP TABLE TEMP_1;
LOWER_LIMIT := UPPER_LIMIT + 1;
UPPER_LIMIT := UPPER_LIMIT + 2000000;


End LOOP;

We have 20 million records in product table and I need to generate order table based on the products table using the above stored procedure. I have tried to optimize as much as I can, but it still takes more than 8 hours to execute on Oracle Standard One. How can I optimize this code?

Answer 1

You select data into a temp table, then copy the content of the temp table into a table. Why not select the data directly into the table?

Insert INTO ORDER_TB
select order_seq.nextval oid
      ,a.pid
      ,a.product_id
      ,a.seq_id
      ,a.value
      ,b.type_id
  from (select a.pid
              ,a.va_id
              ,a.seq_id
              ,a.value
              ,b.type_name
          from (select a.pid
                      ,b.product_id
                      ,b.seq_id
                      ,product_name value
                  from product_table a
                  join va_in_tb b
                    on replace(a.product_name, '''', '') = replace(b.col_nm, '''', '')
                   and a.pid between lower_limit and upper_limit
                union all
                select a.pid
                      ,b.product_id
                      ,b.seq_id
                      ,price value
                  from product_table a
                  join va_in_tb b
                    on replace(a.company_n, '''', '') = replace(b.col_nm, '''', '')
                   and a.pid between lower_limit and upper_limit) a
          left join va_tb b
            on a.product_id = b.product_id) a
  left join va_tb b
    on a.type_name = b.product_name

Answer 2

Please see below how you can use collection to achieve your goal. Reason i asked at the beginning if your code is working fine or not coz i see many errors and in any case it will not run. I haven't tested the code as I can see you posted only a part of your code and also many things are pretty not clear. My code is just to give a snippet which you can implement.

 Declare

    REC_COUNT number;
    Loop_CT   number;
    LOWER_LIMIT number;
    UPPER_LIMIT number;

      Cursor cur_rec (LWR_LMT number,UPR_LMT number)  is 
       /******Assuming all your joins are working fine ..However it doesnot look so from the code your posted. ***/     
       SELECT  ORDER_SEQ.NEXTVAL OID,
                     A.PID,
                     A.PRODUCT_ID,
                     A.SEQ_ID,
                     A.VALUE,
                     B.TYPE_ID 
             FROM 
                 (
                  SELECT  A.PID,
                        A.VA_ID,
                        A.SEQ_ID,
                        A.VALUE,
                        B.TYPE_NAME 
                FROM
                    (
                    select  A.PID, 
                            B.PRODUCT_ID,
                            B.SEQ_ID, 
                            PRODUCT_NAME VALUE 
                    from PRODUCT_TABLE A 
                    JOIN VA_IN_TB B 
                        ON REPLACE(A.PRODUCT_NAME,'''','')= REPLACE(B.PRODUCT_NAME,'''','')                     
                        AND A.PID BETWEEN LWR_LMT AND UPR_LMT
                    union all
                    select  A.PID,
                            B.PRODUCT_ID,
                            B.SEQ_ID, 
                            PRICE VALUE 
                    from PRODUCT_TABLE A 
                    JOIN VA_IN_TB B 
                        ON REPLACE(A.COMPANY_NM,'''','')=REPLACE(B.PRODUCT_NAME,'''','') ----Seems Not correct.
                        AND A.PID BETWEEN LWR_LMT AND UPR_LMT
                    ) A
                LEFT JOIN VA_TB B 
                    ON A.PRODUCT_ID = B.PRODUCT_ID
                ) A
              LEFT JOIN VA_TB B 
                ON A.TYPE_NAME = B.PRODUCT_NAME ;  ----Seems Not correct.

    type var is table of cur_rec%rowtype;

    var_order_tab var;


    begin

    /**Procedure will fail if you try to do anything like this**/
    ---REC_COUNT:= SELECT COUNT(*) FROM PRODUCT_TABLE;

     SELECT COUNT(*)
      into  REC_COUNT
      FROM PRODUCT_TABLE;


     Loop_CT :=( REC_COUNT/2000000)+1;

     SELECT MIN(PID) 
     INTO LOWER_LIMIT 
     FROM PRODUCT_TABLE;

     UPPER_LIMIT := LOWER_LIMIT +2000000;


     Open cur_rec(LOWER_LIMIT,UPPER_LIMIT);
     loop

     fetch cur_rec bulk collect into var_order_tab limit 100;

     FORALL i IN 1 .. var_order_tab.count
           INSERT INTO ORDER_TB
          VALUES var_order_tab(i);

     commit;

     end loop;

   close cur_rec ;

     /**** You cannot create any table like this in any procedure. This is not allowed in PLSQL.
     FOR i in 1..LOOP_COUNT LOOP
     Create Table Temp_1 Nologging as
     SELECT  ORDER_SEQ.NEXTVAL OID,
            A.PID,A.PRODUCT_ID,
            A.SEQ_ID,
            A.VALUE,
            B.TYPE_ID 
    FROM 
        (
        SELECT  A.PID,
                A.VA_ID,
                A.SEQ_ID,
                A.VALUE,
                B.TYPE_NAME 
        FROM
            (
            select  A.PID, 
                    B.PRODUCT_ID,
                    B.SEQ_ID, 
                    PRODUCT_NAME VALUE 
            from PRODUCT_TABLE A 
            JOIN VA_IN_TB B 
                ON REPLACE(A.PRODUCT_NAME,'''','')=REPLACE(B.COL_NM,'''','') 
                AND A.PID BETWEEN LOWER_LIMIT AND UPPER_LIMIT
            union all
            select  A.PID,
                    B.PRODUCT_ID,
                    B.SEQ_ID, 
                    PRICE VALUE 
            from PRODUCT_TABLE A 
            JOIN VA_IN_TB B 
                ON REPLACE(A.COMPANY_N,'''','')=REPLACE(B.COL_NM,'''','')
                AND A.PID BETWEEN LOWER_LIMIT AND UPPER_LIMIT
            ) A 
        LEFT JOIN VA_TB B 
            ON A.PRODUCT_ID=B.PRODUCT_ID
        ) A 
    LEFT JOIN VA_TB B 
        ON A.TYPE_NAME=B.PRODUCT_NAME ****/

    --Insert INTO ORDER_TB
    --SELECT * FROM TEMP_1;
    --Commit;

    /****Neither executing drop command directly is permitted***/
    --DROP TABLE TEMP_1;
    LOWER_LIMIT := UPPER_LIMIT + 1;
    UPPER_LIMIT := UPPER_LIMIT + 2000000;


    --End LOOP;


    end;