RE: Data load options

I will second Lisa's recommendations.

If you have much data, and/or will be doing this frequently, SQL*Loader is your friend. It is very fast.

Loading from a SQL script is very bad unless you have small amounts of data.

I once worked with a duhveloper who could not be dissuaded from loading all data from SQL scripts. We're talking millions of rows here. A single 10 gigabyte SQL script of insert statements.

She single handedly brought our DW to it's knee's. It begged for mercy. There was none. Duhveloper was fired. :)

Jared

                                                                                       
                                
                    "Koivu, Lisa"                                                      
                                
                    <lisa.koivu_at_efair       To:     Multiple recipients of list 
ORACLE-L <ORACLE-L_at_fatcity.com>        
                    field.com>              cc:                                        
                                
                    Sent by:                Subject:     RE: Data load options         
                                
                    root_at_fatcity.com                                                   
                                
                                                                                       
                                
                                                                                       
                                
                    07/25/01 07:26 AM                                                  
                                
                    Please respond to                                                  
                                
                    ORACLE-L                                                           
                                
                                                                                       
                                
                                                                                       
                                

Hi Sean,

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

1. SQL Script. Can be SLOW May require intermittent commits in your script Manual and error prone Must go through SQL engine
2. 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.
3. 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).

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