Re: controlling SQLLdr's commit operations

Menelaos Perdikeas wrote:
>
> "Ron Reidy" <rereidy_at_indra.com> wrote
> news:3BF031E8.7543E07F_at_indra.com...
> > IMHO, You should always load into tables that are not a permanent
> part
> > of your application's "normal" schema (i.e. staging tables). If
> there
> > is a problem, you can always truncate and start again after the fix.
> > From there you can use PL/SQL or some other method to move into your
> > "normal" tables.
>
> Thank you for your responses.
> Since building a PLSQL program to load the tables into oracle using
> UTL_file is not very difficult
> and has the benefit of leaving transaction control totally under my
> control I was wondering whether doing:
>
> file --> PLSQL with readline, string manipulation and dynamic SQL
> insert clauses --> DB
>
> is significantly slower than :
>
> file --> SQLLDR --> staging table --> PLSQL moving data from staging
> table to real table --> DB
>
> ?
>
> If not, is it then true to say that the only merits in the staging
> table approach is that the PLSQL code in the
> second case (which uses staging tables) is probably simpler and also
> that the staging table approach can leverage
> on SQLLDR's advanced capabilities ?
You will probably **never** beat SQL*Loader by using UTL_FILE. Besides, why re-invent the wheel here? SQL*loader comes with Oracle, and is very easy to use.

Some performance things to think about:

1. SQL*Loader indirect loads will be faster than rolling your own load programs regardless of which tool - Perl, C, C++, Java, PL/SQL, etc. you use. SQL*loader has many options built in for error detection, logging, commit strength, etc.
2. SQL*Loader direct loads are even faster than indirect loads. This is because this method bypasses the SGA and build datablocks in the data files.

Given the choice, I would rather not write a customized DB loader. I would also use the direct option and write PL/SQL to move the data from staging tables into the production tables.

-- 
Ron Reidy
[Quoted] Oracle DBA
Reidy Consulting, L.L.C.