Re[2]:RE: Data load options

From: <dgoulet_at_vicr.com>
Date: Wed, 25 Jul 2001 11:21:12 -0700
Message-ID: <F001.00354EED.20010725113101@fatcity.com>

Mohammad,

That only works if you can truncate the table.

Dick Goulet

____________________Reply Separator____________________
Author: "Mohammad Rafiq" <rafiq9857_at_hotmail.com>
Date:       7/25/2001 9:16 AM

Truncation was the solution in this case as I observed in the past that direct load option use empty blocks above HWM thus using lot of space above it. If it is possible target table should be truncated before such loads...

Regards

MOHAMMAD RAFIQ Reply-To: ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: Wed, 25 Jul 2001 07:03:22 -0800

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.

Lisa Koivu The Vicodin-enhanced DBA

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


_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mohammad Rafiq
  INET: rafiq9857_at_hotmail.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: 
  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 - 13:21:12 CDT