PL/SQL collections SLOW!

I have a table with nested table column.

From within PL/SQL I have two approaches I could take.

6-10k rows are processed.

for every row I process, i insert into the nested table a bit of info

1. I could build a pl/sql collection of the same data type, and make one trip to the database updating the nested db table column with the pl/sql structure in memory.

or

2. I could make 10,000 single inserts into the nested table.

I can't for the life of me understand why approach two is performing much better than the first approach.

I have no idea how big the nested table will need to be up front, so I EXTEND the table with every row. I know in other languages this dynamic addition to the array is costly in time, and best done in larger increments (10 at a time, 100 at a time, etc...)

could this be the sole reason. I would think I would experience some benefit from not making the 5000 trips to the database.

I noticed that the more rows i process the closer the PL/SQL collection got to matching the time from the single inserts.

any thoughts on how to investigate further.

Job Received on Thu Jan 10 2002 - 11:37:51 CST