Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: perl DBI question: fetchrow_array

Re: perl DBI question: fetchrow_array

From: Tim Bunce <>
Date: Fri, 07 Mar 2003 07:09:08 -0800
Message-ID: <>

On Thu, Mar 06, 2003 at 02:54:19PM -0800, wrote:
> 1) fetchrow_arrayref is faster than fetchrow_array, as Alex has noted.
> 2) I see you've already set RowCacheSize. Anecdotal evidence ( not just mine)
> suggests that the diminished returns obtained by setting this >100 aren't
> worth it.
> 3) try selectall_arrayref if you're data is not really large. 'really
> large' depends on your environment.
> 4) join DBI users list, found at

That's all true.

I'd just add that recent DBI versions let you specify a max_rows parameter to the fetchall_arrayref method. You can then call it in a loop to get rows on batches.

This is now the fastest way to fetch rows in a loop using the DBI:

  my $rows = []; # cache for batches of rows   while( my $row = ( shift(@$rows) || # get row from cache, or reload cache:

                     shift(@{$rows=$sth->fetchall_arrayref(undef,10_000)||[]) )
  ) {

The code that implements fetchall_arrayref is written in C and, although there's a default implementation in the DBI, a faster one gets embedded into drivers like DBD::Oracle when it's (re)built (after you've upgraded the DBI).

Several parts of the DBI have been optimized with this code-embedding technique so if you've not upgraded your DBI to >= 1.29, or not rebuilt your DBD::Oracle since then it may be worth doing so.

(FYI, if this prompts you to upgrade your DBI installation, please note DBI 1.32 was a good release, but that 1.33 and 1.34 have problems, including a memory leak. I hope to release a 1.35 before Monday.)


p.s. There's some signes of life returning to DBD::Oracle these days. Jeff Urlwin is helping me get the ball rolling again by integrating a selection of patches from the major backlog I have. I also have a new Solaris box and can now install Oracle 8 and 9 again...

Please see the official ORACLE-L FAQ:
Author: Tim Bunce

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message
to: (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Fri Mar 07 2003 - 09:09:08 CST

Original text of this message