Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: External Table vs Sql Loader

Re: External Table vs Sql Loader

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 10 Feb 2007 17:00:31 -0800
Message-ID: <1171155631.540986.97180@j27g2000cwj.googlegroups.com>


On Feb 9, 9:11 pm, "Anurag Varma" <avora..._at_gmail.com> wrote:
> On Feb 9, 7:11 pm, "Patrice Borne" <patbo..._at_gmail.com> wrote:
>
>
>
>
>
> > On Feb 9, 2:37 pm, "Mark D Powell" <Mark.Pow..._at_eds.com> wrote:
>
> > > On Feb 9, 2:09 pm, "Bruce G." <uv_katastro..._at_yahoo.com> wrote:
>
> > > > On Feb 9, 12:11 pm, "Anurag Varma" <avora..._at_gmail.com> wrote:
>
> > > > > On Feb 9, 11:32 am, "Bruce G." <uv_katastro..._at_yahoo.com> wrote:
>
> > > > > > I am on Oracle 9i with a HPUX platform and we are using Sql loader to
> > > > > > load tables from flat files. With external tables now available to
> > > > > > handle this task, I was wondering which performs better. A direct
> > > > > > load with sqlldr or using the external table.
>
> > > > > > I am leaning towards the ET because it will eliminate the need for a
> > > > > > staging table that we use on the sql loader method.
>
> > > > > If you use insert /*+ append */ .... select * from external_table ...
> > > > > You'd find the performance similar.
> > > > > You should use external tables unless there is a compelling
> > > > > case to use sql*loader (like loading from a remote client).
>
> > > > > For more details, see this thread in asktom:http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6...
>
> > > > > Anurag
>
> > > > That answered my question perfectly. It looks like I am about 5 years
> > > > behind the times. :)- Hide quoted text -
>
> > > > - Show quoted text -
>
> > > Actually there are many reasons to use sqlldr instead of an external
> > > table. The need to trigger the process upon receipt of the file from
> > > an external source, the availabitiy of a production job management
> > > system to control the timing of the processing and to trigger followup
> > > steps, the need to perform various filtering, editing, and
> > > manipulation unpon the raw data prior to loading.
>
> > > Use an external table where the process logic works best when the data
> > > is pulled in the database. Use sqlldr where the step fits best in an
> > > external job stream.
>
> > > HTH -- Mark D Powell --
>
> > > The primary driver of which to use is from where do you need to
> > > trigger the operation in your processing? For example if the data
> > > requires filtering and manipulation before it can be used in the
> > > database and this is done via shell scripts then when
>
> > I agree that SQL Loader is much easier to integrate in a global
> > integration process. Also, a big difference between using a staging
> > table populated with SQL Loader and an external table is that you can
> > index the staging table (since it has a ROWID) but you cannot index an
> > external table (at least you couldn't last time I checked).
>
> > SQL Loader is also VERY fast if your objective is to simply load a
> > staging table quickly. Simply bypass the SQL engine with a direct load
> > to write into the blocks of the table directly.
>
> > Finally, an external table is read-only (this may change in the
> > future).
>
> > As usual, there is no yes/no answer here. Depending on the problem at
> > hand, use one or the other tool.
>
> I kinda disagree with both you and Mark. There might be other reasons
> to not use external tables in 9i (unsupported datatypes CLOB/BLOB etc)
> however
> * Speed is not a reason. Did you take a look at the link I mentioned
> above?
> * External tables are NOT meant for general purpose querying. If you
> feel the need to index, you can load data from an external table
> onto a
> heap table and index that!
> * I'm not sure what you mean by sql*loader being easier to integrate
> in global
> integration process? What exactly are you referring to here?
>
> In 10g external tables have gotten more versatile.
> * Most datatypes are supported.
> * You can use external table to write (using ORACLE_DATAPUMP driver),
> It creates a binary file and is meant to transfer data between two
> oracle environments.
> * Oracle in its 10g doco recommends the following on sql*loader vs
> external tables:
> http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14215/
> ldr_concepts.htm#sthref519
> * The case for processing data outside of database has become much
> weaker in 10g
> with regular expressions etc.
> * Marks arguments also start becoming weaker in 10G with the scheduler
> jobs.
>
> External tables have an additional good point of integrating your
> metadata in the database.
> So instead of control files strewn all over the place, you can clean
> em up and
> put them as external tables. I consider this to be a big plus point
> for external tables...
>
> Anurag- Hide quoted text -
>
> - Show quoted text -

The Oracle scheduler, dbms_scheduler, does not provide the standard job dependency capabilities of true job management systems. These systems offer file dependencies, job depenencies, allow creation of job streams where each job has a whole range of dependencies, are sensitive to the return code etc... You can do some of this in Oracle with code; however, you are re-inventing the wheel if you do. Another important feature is some environments where multiple platforms exist is the job scheduling software can submit jobs to multiple platforms. The dbms_scheduler package is a big improvement over dbms_jobs but it is still lacking compared to what is available at the OS level.

Sometimes you it works best to push the data and sometimes it is more convient to pull the data. Choose the tool for the job based on requirements.

IMHO -- Mark D Powell -- Received on Sat Feb 10 2007 - 19:00:31 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US