On Feb 11, 7:33 pm, HansF <Fuzzy.Greybe...@gmail.com> wrote:
> 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.- Hide quoted text -
>
> - Show quoted text -

A chain looks to be much like a traditional job and the ability to
execute each step based on the prior step working successfully is
good, but the feature is still limited. I would say that
dbms_scheduler is now probably good enough for some smaller shops that
have simple requirements and do not want to spend $$ on a scheduler
product to get away without buying one or the alternate of having to
script all the dependencies manually.

Thanks for pointing this new mid-release feature enhancement out.

-- Mark D Powell --

On Feb 11, 1:00 am, "Mark D Powell" <Mark.Pow...@eds.com> wrote:
> 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 --

Quite agree about choosing the right tool for the job. However I've
found external tables massively simpler to work with because all of
the metadata is in the database, and because you can drive the load
process from PL/SQL (and thus from the application if required). An
external job scheduler can simply execute a stored procedure. I've
also used materialized views over the external tables, so the direct
path load became a simple refresh command, with even more self-
documenting metadata (the last refresh date, the SQL query, the
dependencies etc). In one case we then partition-exchanged the MV into
the target table. Alternatively a MERGE approach could work well.

I think where they are more awkward to manage is where you receive a
stream of files with sequential names. You could still achieve this
with a dynamic ALTER TABLE (potentially setting it to a list of files
and loading in parallel) but you would need to write a bunch of code
to manage it.