Re: External Table vs Sql Loader

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. Received on Fri Feb 09 2007 - 18:11:27 CST