Optimize stored procedure loop to improve performance
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_TBselect 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
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;