Re:RE: Data load options

From: <dgoulet_at_vicr.com>
Date: Wed, 25 Jul 2001 08:19:29 -0700
Message-ID: <F001.003547D3.20010725070322@fatcity.com>

Lisa,

SQL*Loader in direct path can cause you storage problems since it always appends to the table(s) ignoring blocks on the free block list. I found that out after a developer was using it for our data warehouse. At the end of a year he had burned almost 100GB of disk & it kept increasing even though he was trying to keep only a rolling 180 window. On a hunch we exported the data, truncated the table & imported the data back in. The amount of tablespace being used dropped 50%.

Dick Goulet

____________________Reply Separator____________________
Author: "Koivu; Lisa" <lisa.koivu_at_efairfield.com>
Date:       7/25/2001 6:26 AM

Hi Sean,

Others have given you different options. Here's some considerations:

SQL Script. * Can be SLOW * May require intermittent commits in your script * Manual and error prone * Must go through SQL engine
SQL Loader * Can be very fast (direct - bypassing SQL engine). * Direct path load will invalidate indexes. * You can keep track of rejected records easily (.bad file) * Dependent upon your file format being EXACT, no errors.
Import * Requires minimal manual fiddling * Can be very slow - I once imported 150GB and it took ~3 days * Does not seamlessly handle all object types (INtermedia (domain) indexes is one example) * Restarting an import will take a lot longer. If your import fails, be sure and truncate/drop all tables before starting again. * Have the option of not including indexes (INDEXES=N) * Can also reset your storage parms prior to import (export with data=n, edit file [SSSHHH] and enter new storage parms / import, export with data=y, import data with IGNORE=Y)

I'm sure others will have additional considerations to share. My choice has always been exp/imp.

Lisa Koivu
The Vicodin-enhanced DBA
Ft. Lauderdale, FL, USA

> -----Original Message-----
> From: O'Neill, Sean [SMTP:Sean.ONeill_at_organon.ie]
> Sent: Wednesday, July 25, 2001 6:16 AM
> To: Multiple recipients of list ORACLE-L
> Subject: Data load options
>
> I'm trying to compile a list of options for loading data into an Oracle
> database on NT platform. What I've come up with so far is:
> [1] SQL script that performs inserts, updates.
> [2] SQL Loader utility
> [3] Import utility
>
> Are there others?
> Anyone care to share experience based opinions on pros and cons of the
> methods?
>
>
> Sean :)
>
> Rookie Data Base Administrator
> [0%] OCP Oracle8i DBA
> [0%] OCP Oracle9i DBA
> -------------------------------- ------------
> Organon (Ireland) Ltd.
> E-mail: sean.oneill_at_organon.ie [subscribed: Digest Mode]
>
> Visit: http://groups.yahoo.com/group/Oracle-OCP-DBA
>
> "Nobody loves me but my mother... and she could be jivin' too." - BB King
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: O'Neill, Sean
> INET: Sean.ONeill_at_organon.ie
>
> 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).

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=US-ASCII">
<META NAME="Generator" CONTENT="MS Exchange Server version 5.5.2653.12">
<TITLE>RE: Data load options</TITLE>
</HEAD>
<BODY>

Hi Sean,

Others have given you different options.  Here's some considerations:

1.  SQL Script.

<UL><LI>Can be SLOW </LI> <LI>May require intermittent commits in your script</LI>
<LI>Manual and error prone</LI> <LI>Must go through SQL engine</LI>

<BR>
</UL>
<P><FONT COLOR="#0000FF" SIZE=2 FACE="Arial">2.&nbsp; SQL Loader</FONT>

<UL><LI>Can be very fast (direct - bypassing SQL engine). </LI>
<LI>Direct path load will invalidate indexes. </LI>
<LI>You can keep track of rejected records easily (.bad file)</LI>
<LI>Dependent upon your file format being EXACT, no errors.</LI>

<BR>
</UL>
<P><FONT COLOR="#0000FF" SIZE=2 FACE="Arial">3.&nbsp; Import</FONT>

<UL><LI>Requires minimal manual fiddling</LI>
<LI>Can be very slow - I once imported 150GB and it took ~3 days</LI>
<LI>Does not seamlessly handle all object types (INtermedia (domain) indexes is one example)</LI> <LI>Restarting an import will take a lot longer.  If your import fails, be sure and truncate/drop all tables before starting again. </LI>
<LI>Have the option of not including indexes (INDEXES=N)</LI>
<LI>Can also reset your storage parms prior to import (export with data=n, edit file [SSSHHH] and enter new storage parms / import, export with data=y, import data with IGNORE=Y)</LI>

<BR>
</UL>
<P><FONT COLOR="#0000FF" SIZE=2 FACE="Arial">I'm sure others will have

additional considerations to share.  My choice has always been exp/imp.

<P><FONT COLOR="#0000FF" SIZE=2 FACE="Arial">Lisa Koivu</FONT>
<BR><FONT COLOR="#0000FF" SIZE=2 FACE="Arial">The Vicodin-enhanced DBA</FONT>
<BR><FONT COLOR="#0000FF" SIZE=2 FACE="Arial">Ft. Lauderdale, FL, USA </FONT>
</P>
<UL>
<P><FONT SIZE=1 FACE="Arial">-----Original Message-----</FONT>
<BR><B><FONT SIZE=1 FACE="Arial">From:&nbsp;&nbsp;</FONT></B> <FONT SIZE=1

FACE="Arial">O'Neill, Sean [SMTP:Sean.ONeill_at_organon.ie] Sent:   Wednesday, July 25, 2001 6:16 AM To:     Multiple recipients of list ORACLE-L Subject:        Data load options

I'm trying to compile a list of options for loading data into an Oracle
 database on NT platform.  What I've come up with so far is:
 [1] SQL script that performs inserts, updates.

<BR><FONT SIZE=2 FACE="Arial">[2] SQL Loader utility</FONT>
<BR><FONT SIZE=2 FACE="Arial">[3] Import utility</FONT>
</P>

Are there others? Anyone care to share experience based opinions on pros and cons of the

<BR><FONT SIZE=2 FACE="Arial">methods?</FONT>
</P>
<BR>

Sean :)

<P><FONT SIZE=2 FACE="Arial">Rookie Data Base Administrator</FONT>
<BR><FONT SIZE=2 FACE="Arial">[0%] OCP Oracle8i DBA</FONT>
<BR><FONT SIZE=2 FACE="Arial">[0%] OCP Oracle9i DBA</FONT>
<BR><FONT SIZE=2 FACE="Arial">-------------------------------- ------------
</FONT>
<BR><FONT SIZE=2 FACE="Arial">Organon (Ireland) Ltd.</FONT>
<BR><FONT SIZE=2 FACE="Arial">E-mail: sean.oneill_at_organon.ie&nbsp;&nbsp;

[subscribed: Digest Mode]

Visit: <A
HREF="http://groups.yahoo.com/group/Oracle-OCP-DBA" TARGET="_blank">http://groups.yahoo.com/group/Oracle-OCP-DBA</A>

"Nobody loves me but my mother... and she could be jivin' too."  - BB King

-- 
 Please see the official ORACLE-L FAQ: <A HREF="http://www.orafaq.com" TARGET="_blank">http://www.orafaq.com</A>

<BR><FONT SIZE=2 FACE="Arial">-- </FONT>
<BR><FONT SIZE=2 FACE="Arial">Author: O'Neill, Sean</FONT>
<BR><FONT SIZE=2 FACE="Arial">&nbsp; INET: Sean.ONeill_at_organon.ie</FONT>
</P>

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051 San Diego, California        -- Public Internet access / Mailing Lists
 <FONT SIZE=2

FACE="Arial">-------------------------------------------------------------------

-
 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).

</P>
</UL>
</BODY>
</HTML>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: dgoulet_at_vicr.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).

Received on Wed Jul 25 2001 - 10:19:29 CDT