Re: External Table vs Sql Loader

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 Received on Fri Feb 09 2007 - 20:11:59 CST