Bulk Collect pivot problem, missing rows!!

I am using a cursor to SELECT from few tables, union the output of SELECTs, and insert into the table by pivoting the cursor columns into another table. So 4 columns that are selected are pivoted to 3 columns in the target table, col1 and col4 are inserted once with col2 and once with col3. This exercise blows into a 20million row table, and takes an hour to load.

To speed things up, I tried to use BULK COLLECT with LIMIT and FORALL (first time for me :)), but ran into some problems, the added rows are significantly less, almost half, although the code runs much faster.

The code is attached below. The actual procedure has lot more SELECTS that are unioned, but to keep it simple I am reducing the CURSOR declare portion to two SELECT UNIONS only.

The source table columns --
PERNR VARCHAR2(8) datatype.
BEGDA,ENDDA,ENDDA_DW DATE datatype.

The target table columns --
PERNR VARCHAR2(8) datatype.
BEGDA,PERNR_BEGDA DATE datatype.

First question is where do the rows disappear while inserting, is it because once the rows are bulk collected and inserted to the table with the first INSERT statement, it is cleaned up from buffer cache and is not available for the second INSERT to load? Second question is if I change the LIMIT parameter, the number of rows loaded into the target table also changes, why is that? Third question, how to get the INSERTs to happen properly, ie without losing records.

Mucho thanks,

Rohit

• Begin Code ****

CREATE OR REPLACE procedure merge_main_proc is

rows NATURAL := 10000;

type tab1_type is table of merge_main_tab.pernr%TYPE;
type tab2_type is table of merge_main_tab.begda%TYPE;
type tab3_type is table of merge_main_tab.begda%TYPE;
type tab4_type is table of merge_main_tab.pernr_begda%TYPE;

pernr_tab tab1_type;
begda1_tab tab2_type;
begda2_tab tab3_type;
pernr_begda_tab tab4_type;

cursor c1 is

	(select /*+ PARALLEL(t1, 5) PARALLEL(t2, 5) USE_HASH(t1) */
	t1.PERNR PERNR,
	t2.BEGDA BEGDA1,
	t2.ENDDA_DW BEGDA2,
	t1.BEGDA PERNR_BEGDA
	from
	F_PA0000_PA0302_EVENT t2,
	MERGE_CHANGES_TAB t1
	where
	t1.PERNR = t2.PERNR
	AND t1.BEGDA <= t2.ENDDA
	AND t1.ENDDA >= t2.BEGDA
	AND (t1.BEGDA <= t2.BEGDA
	OR t1.ENDDA >= t2.ENDDA)
	AND t2.DELETE_DT is null
	AND t2.SPRPS = ' ')
	UNION
	(select /*+ PARALLEL(t1, 5) PARALLEL(t2, 5) USE_HASH(t1) */
	t1.PERNR PERNR,
	t2.BEGDA BEGDA1,
	decode(t2.ENDDA, to_date('9999-12-31', 'YYYY-MM-DD'), t2.ENDDA,
(t2.ENDDA + 1)) BEGDA2,
	t1.BEGDA PERNR_BEGDA
	from
	SAP_PA9001 t2,
	MERGE_CHANGES_TAB t1
	where
	t1.PERNR = t2.PERNR
	AND t1.BEGDA <= t2.ENDDA
	AND t1.ENDDA >= t2.BEGDA
	AND (t1.BEGDA <= t2.BEGDA
	OR t1.ENDDA >= t2.ENDDA)
	AND t2.DELETE_DT is null
	AND t2.SPRPS = ' ');

j PLS_INTEGER := 1;

begin

    open c1;
    loop

        fetch c1 bulk collect into pernr_tab, begda1_tab, begda2_tab, pernr_begda_tab LIMIT rows;

        forall j in pernr_tab.FIRST .. pernr_tab.LAST

            insert into merge_main_tab


            (PERNR, BEGDA, PERNR_BEGDA)


            values


[Quoted]             (pernr_tab(j), begda1_tab(j), pernr_begda_tab(j));


            insert into merge_main_tab


            (PERNR, BEGDA, PERNR_BEGDA)


            values


[Quoted]             (pernr_tab(j), begda2_tab(j), pernr_begda_tab(j));



        exit when c1%NOTFOUND;

     end loop;

     close c1;

    commit work;

end merge_main_proc;

/

• End Code ***