controlling SQLLdr's commit operations
Menelaos Perdikeas
I need to import large amounts of data using the SQL loader tool and
since I want this
to be an atomic operation I was wondering if there is any way to
configure SQL loader
to do a commit only at the (successful) end of a large transaction.
Setting the size of the
bind array (as described in "Determining the size of the Bind Array"
in Oracle's documentation) to a value large enough to hold all the
rows, even if sth. like
that were possible doesn't seem very deterministic to me. So, is there
a pattern in dealing
with this type of situation or does one have to write PLSQL or OCI
calls oneself
to do the importing in the intended way ??
Thanks,
Menelaos Perdikeas.
Ron Reidy
the buffer?
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.
--
Ron Reidy
Oracle DBA
Reidy Consulting, L.L.C.
Marc
gives you the opportunity to validate your data from file and you can do
your data/exception handling also straight from file. However, if you are
going to import large amounts of data a staging table might be the best
solution.
hth
Marc
"Menelaos Perdikeas" <mperd...@semantix.gr> wrote in message
news:9sov0l$1dhc$1...@ulysses.noc.ntua.gr...
Sanjay Mishra
and Windows NT, this limit is 20MB. You can't have a bind array larger
than this. So, if all your data for one load is not accomodated within
20MB, you can't use the bind array size to effect commit at the end of
the transaction.
Also, keep in mind that the larger the bind array, the larger the
rollback segment needs to be.
If the "commit only at the end" is an absolute requirement of your
application, you can use the staging tables as suggested by Ron Reidy.
Staging tables are very commonly used for similar purpose.
For more details, you can refer to SQL*Loader: The Definitive Guide,
by O'Reilly.
Sanjay Mishra
Co-Author, SQL*Loader: The Definitive Guide
Menelaos Perdikeas
"Ron Reidy" <rer...@indra.com> wrote
news:3BF031E8...@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 ?
Ron Reidy
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.