RE: Is sqlplus too slow to unload data?

From: Kimberly Smith <ksmith2_at_myfirstlink.net>
Date: Thu, 25 Apr 2002 06:08:35 -0800
Message-ID: <F001.0044F34A.20020425060835@fatcity.com>

Hey Jared,
I got quite a few folks waiting for your book now. I am really pushing Perl at the office:-) When is the scheduled release again?

-----Original Message-----
Sent: Thursday, April 25, 2002 12:03 AM
To: Multiple recipients of list ORACLE-L

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: jkstill_at_cybcon.com

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: ListGuru_at_fatcity.com (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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Kimberly Smith
  INET: ksmith2_at_myfirstlink.net

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: ListGuru_at_fatcity.com (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 Thu Apr 25 2002 - 09:08:35 CDT