Re: Uploading data into Oracle
Date: Mon, 08 Feb 1999 22:56:13 -0800
Message-ID: <36BFDC0D.391301E9_at_goto.com>
MAMASTA wrote:
>
> What is the best tool for uploading data into oracle?
I hate to say it, but the best tool for the job is SQL*Loader from the Oracle distribution. The command is called 'sqlldr' on Unix. It is tempermental and as poorly documented as everything else written by Oracle, but it is the fastest thing for inserting bulk data.
> It took me a few days just to generate and load ~300,000 record database.
I use sqlldr to load 20-30 million rows every day. The direct loading
path (command line option 'direct=true') is fastest, but it temporarily
disables contraints. It bypasses the SQL query buffers and writes
irectly to the tablespace. If you load duplicates into a unique index,
sqlldr will choke and leave that index in an unusable 'direct load
state', forcing you
to rebuild the index. Also, sqlldr locks the tables during the load. So,
if you feel confident about the data you are loading and can find a
quiet moment on your server, you should be able to load 300,000 rows in
a few minutes.
The default loading path is Conventional. It is much more friendly but
much slower. It performs insert statements in blocks (~100 at a time,
but this is configurable). Constraints are checked, and other sessions
can query and update the data during the load. If you only have to load
10,000 rows at a time, and have a busy machine, this may make the most
sense for you.
The SQL*Loader documentation is in the Utitlies section of the Oracle
docs, and can be found at
http://technet.oracle.com/doc/server.804/a58244/part2.htm#435958 .
Good Luck!
Steven Skovran
skovran_at_goto.com
http://www.goto.com/
Received on Tue Feb 09 1999 - 07:56:13 CET