One way to speed a direct load dramatically is to minimize use of the redo log. There are three ways to do this. You can disable archiving, you can specify that the load is unrecoverable, or you can set the SQL
NOLOGGING parameter for the objects being loaded. This section discusses all methods.
If archiving is disabled, then direct path loads do not generate full image redo. Use the SQL
NOARCHIVELOG parameters to set the archiving mode. See the Oracle Database Administrator's Guide for more information about archiving.
To save time and space in the redo log file, use the SQL*Loader
UNRECOVERABLE clause in the control file when you load data. An unrecoverable load does not record loaded data in the redo log file; instead, it generates invalidation redo.
UNRECOVERABLE clause applies to all objects loaded during the load session (both data and index segments). Therefore, media recovery is disabled for the loaded table, although database changes by other users may continue to be logged.
Because the data load is not logged, you may want to make a backup of the data after loading.
If media recovery becomes necessary on data that was loaded with the
UNRECOVERABLE clause, then the data blocks that were loaded are marked as logically corrupted.
To recover the data, drop and re-create the data. It is a good idea to do backups immediately after the load to preserve the otherwise unrecoverable data.
By default, a direct path load is
The following is an example of specifying the
UNRECOVERABLE clause in the control file:
UNRECOVERABLE LOAD DATA INFILE 'sample.dat' INTO TABLE emp (ename VARCHAR2(10), empno NUMBER(4));