BULK COLLECT - Can it be implemented for a few rows .

I have a question on the usage of BULK COLLECT . About an year ago I wrote a procedure (PROC 1 ) using Old oracle concepts of Cursors and Loops.
Recently I changed the code and re implemented/redesigned the same using BULK COLLECT logic (PROC 2).
I maintained the same functionality .

There are two ways of running PROC 1 and PROC 2 (FULL RUN AND A PARTIAL RUN )
Full run will truncate a table and repopulate all the rows of the table .
partial run will continue from where it left in the previous run.

FULL RUN will populate about 1 million rows while a partial run can populate just 5 to a max 1000 rows .

The PROC 1 is remarkably fast when run on FULL RUN . It takes 11 min only to complete contrary to the PROC 2 which takes 45 minutes .

But for the partial run the PROC 1 is very very slow . It takes 12 min to complete while PROC 2 takes just 20 seconds .

I am highly confused by the functioning of the BULK COLLECT . Should it be used only when there are a lot of rows ? Expecting an answer to this as early as possible since i amnot able to proceed further .

Regards
KN Aravindh Received on Fri Dec 02 2005 - 01:52:46 CET