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.

On Feb 9, 11:32 am, "Bruce G." <uv_katastro...@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/...:6611962171229

Anurag

On Feb 9, 12:11 pm, "Anurag Varma" <avora...@gmail.com> wrote:
> On Feb 9, 11:32 am, "Bruce G." <uv_katastro...@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/..._QUESTION_ID:6...
>
> Anurag

That answered my question perfectly. It looks like I am about 5 years
behind the times.

On Feb 9, 2:09 pm, "Bruce G." <uv_katastro...@yahoo.com> wrote:
> On Feb 9, 12:11 pm, "Anurag Varma" <avora...@gmail.com> wrote:
>
>
>
>
>
> > On Feb 9, 11:32 am, "Bruce G." <uv_katastro...@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/..._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

On Feb 9, 2:37 pm, "Mark D Powell" <Mark.Pow...@eds.com> wrote:
> On Feb 9, 2:09 pm, "Bruce G." <uv_katastro...@yahoo.com> wrote:
>
>
>
> > On Feb 9, 12:11 pm, "Anurag Varma" <avora...@gmail.com> wrote:
>
> > > On Feb 9, 11:32 am, "Bruce G." <uv_katastro...@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/..._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.

On Feb 9, 7:11 pm, "Patrice Borne" <patbo...@gmail.com> wrote:
> On Feb 9, 2:37 pm, "Mark D Powell" <Mark.Pow...@eds.com> wrote:
>
>
>
> > On Feb 9, 2:09 pm, "Bruce G." <uv_katastro...@yahoo.com> wrote:
>
> > > On Feb 9, 12:11 pm, "Anurag Varma" <avora...@gmail.com> wrote:
>
> > > > On Feb 9, 11:32 am, "Bruce G." <uv_katastro...@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/..._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...er.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

On Feb 9, 9:11 pm, "Anurag Varma" <avora...@gmail.com> wrote:
> On Feb 9, 7:11 pm, "Patrice Borne" <patbo...@gmail.com> wrote:
>
>
>
>
>
> > On Feb 9, 2:37 pm, "Mark D Powell" <Mark.Pow...@eds.com> wrote:
>
> > > On Feb 9, 2:09 pm, "Bruce G." <uv_katastro...@yahoo.com> wrote:
>
> > > > On Feb 9, 12:11 pm, "Anurag Varma" <avora...@gmail.com> wrote:
>
> > > > > On Feb 9, 11:32 am, "Bruce G." <uv_katastro...@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/..._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...er.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 --

On Sat, 10 Feb 2007 17:00:31 -0800, Mark D Powell wrote:

> On Feb 9, 9:11 pm, "Anurag Varma" <avora...@gmail.com> wrote:
>
> 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

Have you seen the 10g R2 'events schedule' and 'job chain' enhancements?

--
Hans Forbrich (mailto: Fuzzy.GreyBeard_at_gmail.com)
*** Feel free to correct me when I'm wrong!
*** Top posting [replies] guarantees I won't respond.

On Feb 10, 11:13 pm, HansF <Fuzzy.Greybe...@gmail.com> wrote:
> On Sat, 10 Feb 2007 17:00:31 -0800, Mark D Powell wrote:
> > On Feb 9, 9:11 pm, "Anurag Varma" <avora...@gmail.com> wrote:
>
> > 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
>
> Have you seen the 10g R2 'events schedule' and 'job chain' enhancements?
>
> --
> Hans Forbrich (mailto: Fuzzy.GreyBeard_at_gmail.com)
> *** Feel free to correct me when I'm wrong!
> *** Top posting [replies] guarantees I won't respond.

Possibility not. I will have to look again since the term job chain
does not ring any bells and my doco at home is labeled 10.1. I will
check my 10.2 doc at work.

-- Mark D Powell --

On Sun, 11 Feb 2007 14:32:40 -0800, Mark D Powell wrote:

> On Feb 10, 11:13 pm, HansF <Fuzzy.Greybe...@gmail.com> wrote:
>> On Sat, 10 Feb 2007 17:00:31 -0800, Mark D Powell wrote:
>> > On Feb 9, 9:11 pm, "Anurag Varma" <avora...@gmail.com> wrote:
>>
>> > 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
>>
>> Have you seen the 10g R2 'events schedule' and 'job chain' enhancements?
>>
>
> Possibility not. I will have to look again since the term job chain
> does not ring any bells and my doco at home is labeled 10.1. I will
> check my 10.2 doc at work.
>

You may be pleasantly surprised - at least some of the advanced
functionality you deem to be part of 'true job management systems' is now
part of the job scheduler.

That said, the syntax and UI are typical 'Oracle'. <g>

--
Hans Forbrich (mailto: Fuzzy.GreyBeard_at_gmail.com)
*** Feel free to correct me when I'm wrong!
*** Top posting [replies] guarantees I won't respond.