Thank you, this is exactly what I was looking for.
best wishes
hannes
On 06/02/2010 02:31 PM, John Scoles wrote:
Johannes Gritsch wrote:
DBI does support bulk operations and what you are asking about is the
array interface.
It does it though the 'execute_array'
http://search.cpan.org/~timb/DBI-1.611/DBI.pm#execute_array method.
and it is available to all DBD drivers.
DBD::Oracle is special as it implements its own version of it that takes
advantage of Oracle's array interface. It has been in there since 1.18.
here is an example of its use
my @in_values=('1',2,'3','4',5,'6',7,'8',9,'10');
my @status;
my $sth = $dbh->prepare(qq( INSERT INTO foo (id, bar) VALUES
(foo_id_seq.nextval, ?)));
$sth->bind_param_array(1,\...@in_values);
$sth->execute_array({ArrayTupleStatus=>\...@status});
I have never done a full speed test but with 1.17 it took about 25min to
insert 2 million records using the same code and 1.18 it took only about
6 min.
hope this helps
cheers
John
Hi!
Since I came into this topic while writing a script: What does
DBD::Oracle know about bulk operations?
In PL/SQL you can speed up your cursor loops by using bulk fetches
and/or bulk updates/inserts. Instead of operating on single rows you
use arrays of rows. Since DBI does not support it directly (from what
I know), it could be used when doing select_all_xxx calls. Sounds
simple, and, yes, I know, it is not as easy as it sounds. But I think
it would speed up this calls. PL/SQL gets a boost by 5 to 10,
depending on hardware, OS and other things.
When you search for DBD::Oracle and bulk, google only comes up with
rather old documents dating back to 2004 and 2003. In one of those
(from this mailinglist) I found:
"It is not part of DBD::Oracle yet, though there are plans to include
it in a future version."
I'm a regular reader of this mailing list and I wonder that this topic
did not come up again in recent years - or I just missed it ...
cu
hannes
