Using FORALL with associative arrays

[Tor Hovland]

I have the following procedure:

TYPE testarray is table of int index by binary_integer;

PROCEDURE testfast(par1 in testarray) is
begin
FORALL i IN par1.FIRST..par1.LAST
insert into dummy (test) values (par1(i));
end;

This works, but is not a good solution with giant arrays, as the forall
puts all of it in memory. The obvious solution would be to use BULK
COLLECT and a LIMIT, but that only works with SQL types, and not with
PL/SQL collections, as far as I can tell.

Somewhat oddly, if I call the insert statement directly from my client
using array binding, the perfomance is as good as the procedure and it
scales well with large arrays.

I would think it should be possible to achieve the same performance and
scalability via a procedure, right?

I have to use an associative array, because that's the only collection
type currently supported by ODP.NET, unless I'm mistaken.

--
Tor H.

[Tor Hovland]

Responding to myself here, as nobody else seem willing to :-)

It finally dawned on me that I can simulate LIMIT by taking manual
control over the FORALL range. The following procedure is a very
performant and scalable way to push data into oracle.

PROCEDURE testfast(par1 in testarray) is
startPos int;
endPos int;
begin
startPos := par1.first;

loop
endPos := startPos + 100;

if endPos > par1.last then
endPos := par1.last;
end if;

FORALL i IN startPos .. endPos
insert into dummy (test) values (par1(i));

startPos := endPos + 1;

exit when endPos = par1.last;
end loop;
end;

[HansF]

On Wed, 16 Nov 2005 13:57:43 +0100, Tor Hovland wrote:

Responding to myself here, as nobody else seem willing to :-)

Not necessarily unwilling. Perhaps not visible. Or perhaps just tired
of monitoring a defunct newsgroup that has limited circulation.

Had you spent a brief time looking for the charter for this news group (a
google search would have led you to OraFaq.com) you would have realized
that this group has been superceeded by comp.datases.oracle.server and
comp.databases.oracle.misc which are officially carried by newsgroup
servers (whereas carrying this one is totally optional).

/Hans