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 ARCHIVELOG
and 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.
The 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.
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 RECOVERABLE
.
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));