Re: sqlload performance

In article <3g3boo$25q_at_ixnews1.ix.netcom.com>, chuckh_at_ix.netcom.com (Chuck Hamilton) writes:
|> I want to load and index a database on a monthly basis. There is no
|> referential integrity or constraint checking. The data will be valid.
|> It's being passed to an Oracle data warehouse via sequential files and
|> the data has already been validated.
|>
|> My question is what's the fastest way to load it? I'm planning to use
|> sqlload's direct path, but would it be faster to truncate the tables
|> every month and reload, allowing sqlload to build the indices as it
|> goes? Or would it be faster to drop the indices entirely, load the
|> tables, then re-create the indices with sqlplus?
|>
|> Chuck

Direct path with all indices defined should be the fastest load. The loader is collecting the key values and rowids for all indices while doing the data load. Then it sorts each set of key values into an index. Any approach of dropping and recreating indices will cause a read pass of the data for each index created. If the data is sorted in the order of one of the indices then specifying this index in the loader control file will save one sort. Be aware that if the index is unique, and if the data contains duplicate data the error message will indicate that the data is not sorted instead of indicating there are duplicates.

We have been very satified with the direct path loader for almost all of our tables reloads. The only exception has been with very wide tables (over 100 columns) when the direct path load dies.

Bill Wood Received on Tue Jan 31 1995 - 21:58:45 CET