Since this is my first time to post a question so kindly forgive me if it is Lengthy or somewhere wrong.
Below Code is taking around 40 hours to execute all the pool_IDs . I've tried it with FORALL but Somehow I'm getting wrong data in the target table
T2SEC_AMORTIZATION_SAS Since it is in nested loop so, I'm getting confused how to use FORALL by removing the innermost loop (FOR i IN 0..(MAX_PERIODS_ACCOUNT - 1))
Also, can I use the below PROC call
AMT_CALC_FR_LOAN_PERIOD_SAS with FORALL or do I need to take the whole code out in terms of using the INSERT directly in the parent PROC ?
Or if you have any other suggestion please suggest .......
PROCEDURE AMT_CALCULO_SAS(--Parameters)
TYPE cur_typ IS REF CURSOR;
c cur_typ;
BEGIN
---- We are taking around 16 pool IDs from subaccount table and each pool_id contains contains average of 5 million records.
cursor c_pool is SELECT DISTINCT pool_id from orasecure.tbti_subaccount where extract_dt=p_extract_dt;
open c_pool;
loop
fetch c_pool into v_pool_id; ------ running for each pool_ID
EXIT WHEN c_pool%NOTFOUND;
EXECUTE IMMEDIATE 'TRUNCATE TABLE T2SEC_AMORTIZATION_SAS'; ---- Truncating the target table before Inserting
query_str := 'Huge Dynamic select query is there to fetch the columns';
OPEN c FOR query_str;
LOOP
FETCH c INTO ---- Some declared variables to get the data from the query_str
NUMBER_PERIODS := AMT_Get_Num_Of_Periods(P_EXTRACT_DT, MATURITY_DATE); ----- function to get the maturity dates
MAX_PERIODS_ACCOUNT := NUMBER_PERIODS
------ THEN Huge IF STATEMENTS calculation is going on to calculate the data we got from above cursor ----
FOR i IN 0..(MAX_PERIODS_ACCOUNT - 1) LOOP ---- Giving only numbers eg. 40
------- Huge IF STATEMENTS calculation going on ----------
-------Then the below INSERT PROC for T2SEC_AMORTIZATION_SAS is getting called ------------
AMT_CALC_FR_LOAN_PERIOD_SAS( With all the Parameters values we got from IF statements);
END LOOP;
END LOOP;
CLOSE C;
COMMIT;
------Some Calculations -------
END LOOP;
CLOSE c_pool;
END ;
I've added a little more specifics into your code so I can then show you how I would tailor it
Original
PROCEDURE AMT_CALCULO_SAS(--Parameters)
TYPE cur_typ IS REF CURSOR;
c cur_typ;
v1, v2,v3, v4,v5 int;
BEGIN
cursor c_pool is SELECT DISTINCT pool_id from orasecure.tbti_subaccount where extract_dt=p_extract_dt;
open c_pool;
loop
fetch c_pool into v_pool_id; ------ running for each pool_ID
EXIT WHEN c_pool%NOTFOUND;
EXECUTE IMMEDIATE 'TRUNCATE TABLE T2SEC_AMORTIZATION_SAS'; ---- Truncating the target table before Inserting
query_str := 'Huge Dynamic select query is there to fetch the columns';
OPEN c FOR query_str;
LOOP
FETCH c INTO v1,v2,v3,v4,v5;
NUMBER_PERIODS := AMT_Get_Num_Of_Periods(P_EXTRACT_DT, MATURITY_DATE); ----- function to get the maturity dates
MAX_PERIODS_ACCOUNT := NUMBER_PERIODS
FOR i IN 0..(MAX_PERIODS_ACCOUNT - 1) LOOP
AMT_CALC_FR_LOAN_PERIOD_SAS();
-- inside this proc is:
--
-- insert into mytable values (d1,d2,d3,d4,d5,d6);
--
END LOOP;
END LOOP;
CLOSE C;
COMMIT;
END LOOP;
CLOSE c_pool;
END ;
Proposed
PROCEDURE AMT_CALCULO_SAS(--Parameters)
TYPE cur_typ IS REF CURSOR;
c cur_typ;
type dynamic_record is
( v1, v2,v3, v4,v5 int; )
type dynamic_list is
table of dynamic_record
index by pls_integer;
dyn_list dynamic_list;
type insert_record is
( d1,d2,d3,d4,d5,d6 int; )
type insert_list is
table of insert_record
index by pls_integer;
ins_list insert_list;
BEGIN
--
-- implicit cursor automatically does array fetch
--
for c_pool in ( SELECT DISTINCT pool_id from orasecure.tbti_subaccount where extract_dt=p_extract_dt )
loop
EXECUTE IMMEDIATE 'TRUNCATE TABLE T2SEC_AMORTIZATION_SAS'; ---- Truncating the target table before Inserting
query_str := 'Huge Dynamic select query is there to fetch the columns';
OPEN c FOR query_str;
LOOP
-- grab batches 1000 at a time
FETCH c INTO bulk collect into dyn_list limit 1000;
-- now loop through those 1000 records
for d in 1 .. dyn_list.count
loop
ins_list.delete;
NUMBER_PERIODS := AMT_Get_Num_Of_Periods(P_EXTRACT_DT, MATURITY_DATE); ----- function to get the maturity dates
MAX_PERIODS_ACCOUNT := NUMBER_PERIODS
FOR i IN 0..(MAX_PERIODS_ACCOUNT - 1) LOOP
--
-- this proc now edited to NOT do an insert, but to add the records to array 'ins_list'
--
AMT_CALC_FR_LOAN_PERIOD_SAS();
ins_list(ins_list.count+1).d1 := d1;
ins_list(ins_list.count).d2 := d2;
ins_list(ins_list.count).d3 := d3;
ins_list(ins_list.count).d4 := d4;
ins_list(ins_list.count).d5 := d5;
END LOOP;
--
-- at this point, we now have a list of records to insert
--
forall ins in 1 .. ins_list.count
insert into mytables values ins_list(ins);
end loop;
-- moved to end because we get notfound when we get 0 <= records <= 1000
exit when c%notfound;
END LOOP;
CLOSE C;
END LOOP;
COMMIT;
END ;
The ins_list could even be pushed further out in the loops to make the array larger and do less insert-into calls, but that depends on volume etc.