Batch Fetching -> Out of memory Error

[Shaozab, Sumera]

Hi,

I am using DBI 1.14 and DBD::Oracle 1.06.  

I am trying to write a batch program that will run every 2 months.
Initially, I was using the fetchrow_arrayref method to fetch the data, but
because I had multple sub-queries the program would take too long to run.
The DBA takes down the database every night to do backups and the program is
still running and therefore the program has to terminate.  To help speed up
anything, I decided to use "fetchall_arrayref()".

 According to the Perl DBI book, the fetchall_arrayref() method was
efficient way to fetching all the result set from the database. So when I
tried, I got "Out of memory!" error.  The number of records I am retrieving
is about 3 million records.  Is this too much for fetchall_arrayref()?
Should I upgrade DBI and DBD::Oracle? Any recommendation in speeding up my
program?

This is what I tried:
        
my $sth0 = $dbh -> prepare( 'SELECT a, b, c FROM table1 where d is not null'
);
$sth0->execute;
                                                
my $array_ref = $sth0->fetchall_arrayref();
foreach my $row (@$array_ref) {
        my ($sA,$sB,$sC) = @$row;
        print "$sA:$sB:$sC\n";  
        }

                                
                        
My previouse method which is quite slow:

my $sth0 = $dbh ->   prepare( 'SELECT a, b, c FROM table1 ORDER BY 1' );
   my $sth1 = $dbh ->   prepare( 'SELECT d, e FROM table2 WHERE a = ? ');
   my $sth2 = $dbh ->   prepare( 'SELECT f, g, h FROM table3  WHERE d = ?
');
   my $sth3 = $dbh ->   prepare( 'SELECT i, j, k FROM table4 WHERE f = ? ');
   my $sth4 = $dbh ->   prepare( 'SELECT l, m, n FROM table5 WHERE j = ?' );
  
   $sth0 ->   execute;
   $sth1 ->   execute( '' );
   $sth2 ->   execute( '' );
   $sth3 ->   execute( '' );
   $sth4 ->   execute( '' );

   $sth0 ->   bind_columns( \( $sA, $sB, $sC ) );
   $sth1 ->   bind_columns( \( $sD, $sE ) );
   $sth2 ->   bind_columns( \( $sf, $sg, $sh ) );
   $sth3 ->   bind_columns( \( $si, $sj, $sk ) );
   $sth4 ->   bind_columns( \( $sl, $sm, $sn ) );

   # Use the fetched values
   while ( $sth0 ->   fetch ) {
      $sth1 ->   execute( $sA );
      while ( $sth1 ->   fetch ) {
          # Do Something with $sA ... $sE
      }
  
      $sth2 ->   execute( $sD );
      while ( $sth2 ->   fetch ) {
          # Do Something with $sf ... $sh   }
   $sth3 ->   execute( $sg );
      while ( $sth ->   fetch ) {
          # Do Something with here
     }
   }
>
   $sth4 ->   execute( $sk);
      while ( $sth4 ->   fetch ) {
          # Do Something with $sl ... $sn
      }
  
   }
   $dbh ->   disconnect;
  
   

Any help is appreciated.
Thanks in Advance!

Sumera