Hi Gurus,
I am having some performance issues on bulk loading and have tried to break down pieces below on whats happening and what am I trying.
Would you be able to suggest any way that would be better or I am missing?
1)
create table table_a
(
loan_nbr number,
yr_mnth_cd varchar2(6 byte),
start_dt date,
maturity_dt date ,
number_of_terms number
);
primary_key loan_acct_nbr, yr_mnth_cd (unique indexed)
indexed by loan_nbr,yr_mnth_cd
create table final_load_table
(
loan_nbr number,
yr_mnth_cd varchar2(6 byte),
......,
......,
......);
indexed by loan_nbr,yr_mnth_cd
2) function my_function ( loan_nbr,loan_start_dt,maturity_dt,number_of_terms) return table_type
3) Cursor below is used in a code which joins table_a and function my_function and that resultset is being inserted into final_table
one loan would have 50-65 rows generated by function which is equal to 'NUMBER_OF_TERMS'.
some business logic is applied on for each row and then that row is loaded to collection and returned by function.
so i have roughly not 50-60 million records on each every month,
I have tried few approach on loading this
1- using bulk collect and comitting on every 100K records
2- direct insert
insert into FINAL_LOAD_TABLE
as select * from cursor;
However both of this takes very long time.Is there any approach we can take for this loading?
We are planning for creating a partition on final_load_table based on yr_month_Cd (for each month).
Cursor:
SELECT Y.*,
TRUNC(SYSDATE,'MM'),
USER
FROM TABLE_A A
JOIN TABLE(MY_FUNCTION (A.LOAN_NBR,A.START_DT,A.MATURITY_DT,A.NUM_OF_TERMS)) Y
ON A.LOAN_NBR = Y.LOAN_ACCT_NBR
WHERE A.YR_MNTH_CD = TO_CHAR(ADD_MONTHS(SYSDATE,-1),'YYYYMM') --last month
AND A.LOAN_TYP='ABC'
AND NOT EXISTS ( SELECT 1 FROM FINAL_LOAD_TABLE L
WHERE L.LOAN_ACCT_NBR =Y.LOAN_ACCT_NBR
AND Y.YR_MNTH_CD=L.YR_MNTH_CD );
Thanks