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: 9 Feb 2007 14:37:50 -0800
Message-ID: <1171060670.866155.262500@a75g2000cwd.googlegroups.com>


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 Received on Fri Feb 09 2007 - 16:37:50 CST

Original text of this message

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