Re: DBD::Oracle and bulk operations

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