Re: Is sqlplus too slow to unload data?

[Jared Still]

On Tuesday 23 April 2002 21:53, Bin Wang wrote:
> Hi,
> Our application uses sqlplus + sqlloader to transfer data between
>  databases. It takes nearly four hours to unload to data to flat
>  files(1G), which is far too slow. In the application, the query looks
>  like the following. All those &3,&4,&5 are for sqlldr format.

How about using Perl?

Below is a script I just used to unload a table.  Not very big, but this
is just from one of my test databases at home on a Linux box.

It unloaded about 12,000 rows in less than 2 seconds.  This include
writing them to a file.  This script creates a file of <TABLENAME>.dmp.

$> time ul.pl -database ts01 -username orades -password orades \
    -table 'I$RM_PROPERTY_MAPS'

    1.22s real     1.07s user     0.04s system

Doing the same thing with SQL*plus took 4.46 seconds.

You must have DBI and DBD::Oracle installed to use this.

Jared

====================================================
#!/usr/bin/perl

# ul.pl - unload a table

use warnings;
use FileHandle;
use DBI;
use strict;

use Getopt::Long;

my %optctl = ();

Getopt::Long::GetOptions(
        \%optctl, 
        "database=s",
        "username=s",
        "password=s",
        "table=s",
        "sysdba!",
        "sysoper!",
        "z","h","help");

#setup environment - homegrown package

my($db, $username, $password, $connectionMode);

$connectionMode = 0;
if ( $optctl{sysoper} ) { $connectionMode = 4 }
if ( $optctl{sysdba} ) { $connectionMode = 2 }

if ( ! defined($optctl{database}) ) {
        Usage();
        die "database required\n";
}
$db=$optctl{database};

if ( ! defined($optctl{username}) ) {
        Usage();
        die "username required\n";
}

if ( ! defined($optctl{table}) ) {
        Usage();
        die "table required\n";
}

$username=$optctl{username};
$password = $optctl{password};

my $dbh = DBI->connect(
        'dbi:Oracle:' . $db, 
        $username, $password, 
        { 
                RaiseError => 1, 
                AutoCommit => 0,
                ora_session_mode => $connectionMode
        } 
        );

die "Connect to  $db failed \n" unless $dbh;

# time and adjust row cache size
$dbh->{RowCacheSize} = 5000;

my $MySql="select * from $optctl{table}";

my $sth = $dbh->prepare($MySql);

$sth->execute;

open(OUT,">$optctl{table}.dmp") || die "cannot create $optctl{table}.dmp - 
$!\n";

my $delimiter = '~';

no warnings;  # don't raise warnings on null columns
while( my $ary = $sth->fetchrow_arrayref ) {
        print OUT join($delimiter,@{$ary}), "\n";
}
use warnings;

$sth->finish;
$dbh->disconnect;

sub Usage {
        print "\n";
        print "usage:  ul.pl\n";
        print "    ul.pl -database dv07 -username scott -password tiger -table emp 
[-sysdba || -sysoper]\n";
        print "\n";
}



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jared Still
  INET: [EMAIL PROTECTED]

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).