how to get the best out of BULK opers?

Hello all,

As new capabilities appear I always try to experiment a little bit and also use them in projects, the problem is that the very known PL/SQL BULK (not so new now) operations have really disappointed me somehow e.g.

SELECT .. FROM ...
BULK COLLECT INTO Collect1, Collect2...CollectN

FORALL indx IN Collect1.FIRST .. Collect1.LAST // Use all collections here in a bulk INSERT/UPDATE/MERGE;

is really a waste of good time!!!

a simple INSERT/UPDATE/MERGE INTO...SELECT would several times outperform it... worse a very simplistic loop like

FOR c1 IN CursorX
LOOP
   INSERT/UPDATE/MERGE INTO
    // using collections
END LOOP; many times also outperform the bulking operation when the number of iterations is high (above 100 I would say)... so why bother with bulking? is there any specific rule-of-thumb to follow?

Is it possible that when partitioning tables, then the bulking operations are
run in paralel so that each different processor takes care of a specific partition, would that be possible? then I would use bulking only in cases like this...

TIA,
Best Regards,
Giovanni Received on Fri Feb 07 2003 - 04:55:15 CST