Oracle8i Utilities
Release 8.1.5






Prev Next

SQL*Loader: Conventional and Direct Path Loads


For an example of loading with using the direct path load method, see Case 6: Loading Using the Direct Path Load Method. The other cases use the conventional path load method.

Data Loading Methods

SQL*Loader provides two methods for loading data:

A conventional path load executes SQL INSERT statement(s) to populate table(s) in an Oracle database. A direct path load eliminates much of the Oracle database overhead by formatting Oracle data blocks and writing the data blocks directly to the database files. A direct load, therefore, does not compete with other users for database resources so it can usually load data at near disk speed. Certain considerations, inherent to this method of access to database files, such as restrictions, security and backup implications, are discussed in this chapter.

Conventional Path Load

Conventional path load (the default) uses the SQL INSERT statement and a bind array buffer to load data into database tables. This method is used by all Oracle tools and applications.



Conventional Path Load of a Single Partition

By definition, a conventional path load uses SQL INSERT statements. During a conventional path load of a single partition, SQL*Loader uses the partition-extended syntax of the INSERT statement which has the following form:


The SQL layer of the ORACLE kernel determines if the row being inserted maps to the specified partition. If the row does not map to the partition, the row is rejected, and the loader log file records an appropriate error message.

When to Use a Conventional Path Load

If load speed is most important to you, you should use direct path load because it is faster than conventional path. However, there are certain restrictions on direct path loads that may require you to use a conventional path load. You should use the conventional path in the following situations:

Direct Path Load

Instead of filling a bind array buffer and passing it to Oracle with a SQL INSERT command, a direct path load parses the input data according to the description given in the loader control file, converts the data for each input field to its corresponding Oracle column datatype, and builds a column array structure (an array of <length, data> pairs).




Figure 8-1 Database Writes on Direct Path and Conventional Path

Direct Path Load of a Partitioned or Subpartitioned Table

When loading a partitioned or subpartitioned table, SQL*Loader partitions the rows and maintains indexes (which can also be partitioned). Note that a direct path load of a partitioned or subpartitioned table can be quite resource intensive for tables with many partitions or subpartitions.

Direct Path Load of a Single Partition or Subpartition

When loading a single partition of a partitioned or subpartitioned table, SQL*Loader partitions the rows and rejects any rows which do not map to the partition or subpartition specified in the SQL*Loader control file. Local index partitions which correspond to the data partition or subpartition being loaded are maintained by SQL*Loader. Global indexes are not maintained on single partition or subpartition direct path loads. During a direct path load of a single partition, SQL*Loader uses the partition-extended syntax of the LOAD statement which has the following form:

LOAD INTO TABLE T partition (P) VALUES ... 


LOAD INTO TABLE T subpartition (P) VALUES ... 

While loading a partition of a partitioned or subpartition table, DML operations on, and direct path loads of, other partitions in the table are allowed.

Although a direct path load minimizes database processing, several calls to the Oracle server are required at the beginning and end of the load to initialize and finish the load, respectively. Also, certain DML locks are required during load initialization, and are released when the load completes. Note also that during the load the following operations occur: index keys are built and put into a sort, space management routines are used to get new extents when needed and to adjust the high-water mark for a data save point. The high-water mark is described in Data Saves.

Advantages of a Direct Path Load

A direct path load is faster than the conventional path for the following reasons:

See Instance Recovery and Direct Path Loads.

When to Use a Direct Path Load

If none of the above restrictions apply, you should use a direct path load when:

Restrictions on Using Direct Path Loads

In addition to the general load conditions described in Conventional Path Load versus Direct Path Load, the following conditions must be satisfied to use the direct path load method:

Note also that the following features are not available with direct path load.

Restrictions on a Direct Path Load of a Single Partition

In addition to the above listed restrictions, loading a single partition has the following restrictions:

Integrity Constraints

All integrity constraints are enforced during direct path loads, although not necessarily at the same time. NOT NULL constraints are enforced during the load. Records that fail these constraints are rejected.

UNIQUE constraints are enforced both during and after the load. A record which violates a UNIQUE constraint is not rejected (the record is not available in memory when the constraint violation is detected.)


Field Defaults on the Direct Path

DEFAULT column specifications defined in the database are not available when loading on the direct path. Fields for which default values are desired must be specified with the DEFAULTIF clause, described on DEFAULTIF Clause. If a DEFAULTIF clause is not specified, and the field is NULL, then a NULL value is inserted into the database.

Loading into Synonyms


Exact Version Requirement

You can perform a SQL*Loader direct load only be for databases of the same version. For example, you cannot do a SQL*Loader Version 7.1.2 direct path load to load into a Oracle Version 7.1.3 database.

Using Direct Path Load


Setting Up for Direct Path Loads


Specifying a Direct Path Load



See Case 6: Loading Using the Direct Path Load Method for an example.

Building Indexes

During a direct path load, performance is improved by using temporary storage. After each block is formatted, the new index keys are put to a sort (temporary) segment. The old index and the new keys are merged at load finish time to create the new index. The old index, sort (temporary) segment, and new index segment all require storage until the merge is complete. Then the old index and temporary segment are removed.



Performance on systems with limited memory can also be improved by using the SINGLEROW option. For more information see SINGLEROW Option.

Note: If, during a direct load, you have specified that the data is to be pre-sorted and the existing index is empty, a temporary segment is not required, and no merge occurs--the keys are put directly into the index. See Maximizing Performance of Direct Path Loads for more information.


Index Storage Requirements

The formula for calculating the amount of space needed for storing the index itself can be found in the chapter(s) that describe managing database files" in the Oracle8i Administrator's Guide. Remember that two indexes exist until the load is complete: the old index and the new index.

Temporary Segment Storage Requirements


1.3 * key_storage


key_storage = (number_of_rows) *
     ( 10 + sum_of_column_sizes + number_of_columns )



If the data is fully sorted, only enough space to store the index entries is required, and the value of this constant reduces to 1.0. See Pre-sorting Data for Faster Indexing for more information.

Indexes Left in Index Unusable State

SQL*Loader will leave indexes in Index Unusable state when the data segment being loaded becomes more up-to-date than the index segments that index it.

Any SQL statement that tries to use an index that is in Index Unusable state returns an error. The following conditions cause the direct path option to leave an index or a partition of a partitioned index in Index Unusable state:

To determine if an index is in Index Unusable state, you can execute a simple query:

   WHERE TABLE_NAME = 'tablename';

To determine if an index partition is in unusable state,


If you are not the owner of the table, then search ALL_INDEXES or DBA_INDEXES instead of USER_INDEXES. For partitioned indexes, search ALL_IND_PARTITIONS and DBA_IND_PARTITIONS instead of USER_IND_PARTITIONS.

Data Saves

You can use data saves to protect against loss of data due to instance failure. All data loaded up to the last data save is protected against instance failure To continue the load after an instance failure, determine how many rows from the input file were processed before the failure, then use the SKIP option to skip those processed rows.

If there were any indexes on the table, drop them before continuing the load, then recreate them after the load. See Recovery for more information on media and instance failure.

Note: Indexes are not protected by a data save, because SQL*Loader does not build indexes until after data loading completes. (The only time indexes are built during the load is when pre-sorted data is loaded into an empty table -- but these indexes are also unprotected.)

Using the ROWS Parameter




A data save is an expensive operation. The value for ROWS should be set high enough so that a data save occurs once every 15 minutes or longer. The intent is to provide an upper bound on the amount of work which is lost when an instance failure occurs during a long running direct path load. Setting the value of ROWS to a small number will have an adverse affect on performance.

Data Save Versus Commit


The similarities are:

The major difference is that the indexes will be unusable (in Index Unusable state) until the load completes.



Media Recover  

Recovery from the loss of a database file. You must be operating in ARCHIVELOG mode to recover after you lose a database file.  

Instance Recovery  

Recover from a system failure in which in-memory data was changed but lost due to the failure before it was written to disk. Oracle can always recover from instance failures, even when redo logs are not archived.  

See Oracle8i Administrator's Guide for more information about recovery.

Instance Recovery and Direct Path Loads


If an instance failure occurs, the indexes being built may be left in Index Unusable state. Indexes which are Unusable must be re-built before using the table or partition. See Indexes Left in Index Unusable State for more information on how to determine if an index has been left in Index Unusable state.

Media Recovery and Direct Path Loads



  1. Restore the most recent backup of the affected database file.

  2. Recover the tablespace using the RECOVER command. (See Oracle8i Backup and Recovery Guide for more information on the RECOVER command.)

Loading LONG Data Fields


Loading Data as PIECED

The data can be loaded in sections with the pieced option if it is the last column of the logical record. The syntax for this specification is given High-Level Syntax Diagrams.


The following restrictions apply when declaring a column as PIECED:

Using the READBUFFERS Keyword





Maximizing Performance of Direct Path Loads


To minimize time:

To minimize space:

Pre-allocating Storage for Faster Loading


To calculate the space required by a table, see the chapter(s) describing managing database files in the Oracle8i Administrator's Guide. Then use the INITIAL or MINEXTENTS clause in the SQL command CREATE TABLE to allocate the required space.

Another approach is to size extents large enough so that extent allocation is infrequent.

Pre-sorting Data for Faster Indexing






The SORTED INDEXES statement identifies the indexes on which the data is presorted. This statement is allowed only for direct path loads. See Chapter 5, "SQL*Loader Control File Reference" for the syntax, and seeCase 6: Loading Using the Direct Path Load Method for an example.



Unsorted Data

If you specify an index in the SORTED INDEXES statement, and the data is not sorted for that index, then the index is left in Index Unusable state at the end of the load. The data is present, but any attempt to use the index results in an error. Any index which is left in Index Unusable state must be re-built after the load.

Multiple Column Indexes



Albuquerque      Adams
Albuquerque      Hartstein
Albuquerque      Klein
...         ...
Boston           Andrews
Boston           Bobrowski
Boston           Heigham
...              ...

Choosing the Best Sort Order



  1. For each index, add up the widths of all columns in that index.

  2. For a single-table load, pick the index with the largest overall width.

  3. For each table in a multiple table load, identify the index with the largest, overall width for each table. If the same number of rows are to be loaded into each table, then again pick the index with the largest overall width. Usually, the same number of rows are loaded into each table.

  4. If a different number of rows are to be loaded into the indexed tables in a multiple table load, then multiply the width of each index identified in step 3 by the number of rows that are to be loaded into that index. Multiply the number of rows to be loaded into each index by the width of that index and pick the index with the largest result.

Infrequent Data Saves




Minimizing Use of the Redo Log

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 NOLOG attribute of the objects being loaded. This section discusses all methods.

Disable Archiving

If media recovery is disabled, direct path loads do not generate full image redo.


Use UNRECOVERABLE to save time and space in the redo log file. An UNRECOVERABLE load does not record loaded data in the redo log file, instead, it generates invalidation redo. Note that UNRECOVERABLE applies to all objects loaded during the load session (both data and index segments.)





By default, a direct path load is RECOVERABLE. See SQL*Loader's Data Definition Language (DDL) Syntax Diagrams for information on RECOVERABLE and UNRECOVERABLE.

NOLOG Attribute

If a data or index segment has the NOLOG attribute set, then full image redo logging is disabled for that segment (invalidation redo is generated.) Use of the NOLOG attribute allows a finer degree of control over the objects which are not logged.

Avoiding Index Maintenance

For both the conventional path and the direct path, SQL*Loader maintains all existing indexes for a table.

Index maintenance can be avoided by using one of the following methods:

Avoiding index maintenance saves temporary storage while using the direct load method. Avoiding index maintenance minimizes the amount of space required during the load, for the following reasons:


Direct Loads, Integrity Constraints, and Triggers

With the conventional path, arrays of rows are inserted with standard SQL INSERT statements -- integrity constraints and insert triggers are automatically applied. But when loading data with the direct path, some integrity constraints and all database triggers are disabled. This section discusses the implications of using direct path loads with respect to these features.

Integrity Constraints

During a direct path load, some integrity constraints are automatically disabled. Others are not. For a description of the constraints, see the chapter(s) that describe maintaining data integrity in the Oracle8i Application Developer's Guide - Fundamentals.

Enabled Constraints

The constraints that remain in force are:

Not Null constraints are checked at column array build time. Any row that violates this constraint is rejected. Unique constraints are verified when indexes are rebuilt at the end of the load. The index will be left in Index Unusable state if a violation is detected. See Indexes Left in Index Unusable State.

Disabled Constraints

The following constraints are disabled:

Reenable Constraints


The optional keyword DISABLED_CONSTRAINTS is provided for readability. If the EXCEPTIONS clause is included, the table must already exist and, you must be able to insert into it. This table contains the ROWIDs of all rows that violated one of the integrity constraints. It also contains the name of the constraint that was violated. See Oracle8i SQL Reference for instructions on how to create an exceptions table.





Database Insert Triggers



Replacing Insert Triggers with Integrity Constraints


When Automatic Constraints Cannot Be Used




  1. Before the load, add a one-character column to the table that marks rows as "old data" or "new data".

  2. Let the value of null for this column signify "old data", because null columns do not take up space.

  3. When loading, flag all loaded rows as "new data" with SQL*Loader's CONSTANT clause.


Using An Update Trigger


  1. Create an update trigger that duplicates the effects of the insert trigger.

    Copy the trigger. Change all occurrences of "new.column_name" to "old.column_name".

  2. Replace the current update trigger, if it exists, with the new one

  3. Update the table, changing the "new data" flag to null, thereby firing the update trigger

  4. Restore the original update trigger, if there was one


Duplicating the Effects of Exception Conditions




Using a Stored Procedure


  1. Create a stored procedure that duplicates the effects of the insert trigger. Follow the general outline given below. (For implementation details, see PL/SQL User's Guide and Reference for more information about cursor management.)

    • declare a cursor for the table, selecting all the new rows

    • open it and fetch rows, one at a time, in a processing loop

    • perform the operations contained in the insert trigger

    • if the operations succeed, change the "new data" flag to null

    • if the operations fail, change the "new data" flag to "bad data"

  2. Execute the stored procedure using an administration tool such as SQL*Plus.

  3. After running the procedure, check the table for any rows marked "bad data".

  4. Update or remove the bad rows.

  5. Re-enable the insert trigger.

Permanently Disabled Triggers & Constraints





Alternative: Concurrent Conventional Path Loads

If triggers or integrity constraints pose a problem, but you want faster loading, you should consider using concurrent conventional path loads. That is, use multiple load sessions executing concurrently on a multiple-CPU system. Split the input datafiles into separate files on logical record boundaries, and then load each such input datafile with a conventional path load session. The resulting load has the following attributes:

Parallel Data Loading Models

This section discusses three basic models of concurrency which can be used to minimize the elapsed time required for data loading:

Note: Parallel loading is available only with the Enterprise Edition. For more information about the differences between Oracle8i and the Oracle8i Enterprise Edition, see Getting to Know Oracle8i and the Oracle8i Enterprise Edition.

Concurrent Conventional Path Loads

Using multiple conventional path load sessions executing concurrently is discussed in the previous section. This technique can be used to load the same or different objects concurrently with no restrictions.

Inter-Segment Concurrency with Direct Path

Inter-segment concurrency can be used for concurrent loading of different objects. This technique can be applied for concurrent direct path loading of different tables, or to concurrent direct path loading of different partitions of the same table.

When direct path loading a single partition, the following items should be considered:

Intra-Segment Concurrency with Direct Path

SQL*Loader permits multiple, concurrent sessions to perform a direct path load into the same table, or into the same partition of a partitioned table. Multiple SQL*Loader sessions improve the performance of a direct path load given the available resources on your system.

This method of data loading is enabled by setting both the DIRECT and the PARALLEL option to TRUE, and is often referred to as a "parallel direct path load."

It is important to realize that parallelism is user managed, setting the PARALLEL option to TRUE only allows multiple concurrent direct path load sessions.

Restrictions on Parallel Direct Path Loads

The following restrictions are enforced on parallel direct path loads:

If a parallel direct path load is being applied to a single partition, it is best that the data is pre-partitioned (otherwise the overhead of record rejection due to a partition mismatch slows down the load.)

Initiating Multiple SQL*Loader Sessions

Each SQL*Loader session takes a different datafile as input. In all sessions executing a direct load on the same table, you must set PARALLEL to TRUE. The syntax is:





Note: Indexes are not maintained during a parallel load. Any indexes must be (re)created or rebuilt manually after the load completes. You can use the parallel index creation or parallel index rebuild feature to speed the building of large indexes after a parallel load.

When you perform a PARALLEL load, SQL*Loader creates temporary segments for each concurrent session and then merges the segments upon completion. The segment created from the merge is then added to the existing segment in the database above the segment's high water mark. The last extent used of each segment for each loader session is trimmed of any free space before being combined with the other extents of the SQL*Loader session.

Options Keywords for Parallel Direct Path Loads

When using parallel direct path loads, options are available for specifying attributes of the temporary segment to be allocated by the loader.

Specifying Temporary Segments

It is recommended that each concurrent direct path load session use files located on different disks to allow for the maximum I/O throughput. Using the FILE keyword of the OPTIONS clause you can specify the filename of any valid datafile in the tablespace of the object (table or partition) being loaded. The following example illustrates a portion of one of the control files used for the SQL*Loader sessions in the previous example:

INFILE 'load1.dat'

You can specify the database file from which the temporary segments are allocated with the FILE keyword in the OPTIONS clause for each object (table or partition) in the control file. You can also specify the FILE parameter on the command line of each concurrent SQL*Loader session, but then it will globally apply to all objects being loaded with that session.

Using the FILE Keyword

The FILE keyword in Oracle has the following restrictions for direct path parallel loads:

  1. For non-partitioned tables: the specified file must be in the tablespace of the table being loaded

  2. For partitioned tables, single partition load: the specified file must be in the tablespace of the partition being loaded

  3. For partitioned tables, full table load: the specified file must be in the tablespace of all partitions being loaded that is, all partitions must be in the same tablespace.

Using the STORAGE Keyword

The STORAGE keyword can be used to specify the storage attributes of the temporary segment(s) allocated for a parallel direct path load. If the STORAGE keyword is not used, the storage attributes of the segment containing the object (table, partition) being loaded are used.


For example, the following STORAGE clause could be used:


The STORAGE keyword can only be used in the control file, and not on the command line. Use of the STORAGE keyword to specify anything other than
PCTINCREASE of 0, and INITIAL or NEXT values is strongly discouraged (and may be silently ignored in the future.)

Enabling Constraints After a Parallel Direct Path Load

Constraints and triggers must be enabled manually after all data loading is complete.

General Performance Improvement Hints

This section gives a few guidelines which can help to improve the performance of a load. If you must use a certain feature to load your data, by all means do so. But if you have control over the format of the data to be loaded, here are a few hints which can be used to improve load performance:

  1. Make logical record processing efficient:

    • use one-to-one mapping of physical records to logical records (avoid continueif, concatenate)

    • make it easy for the software to figure out physical record boundaries. Use the file processing option string "FIX nnn" or "VAR". If you use the default (stream mode) on most platforms (e.g. UNIX, NT) the loader has to scan each physical record for the record terminator (newline character.)

  2. Make field setting efficient. Field setting is the process of mapping "fields" in the datafile to their corresponding columns in the table being loaded. The mapping function is controlled by the description of the fields in the control file. Field setting (along with data conversion) is the biggest consumer of CPU cycles for most loads.

    • avoid delimited fields; use positional fields. If you use delimited fields, the loader must scan the input data to find the delimiters. If you use positional fields, field setting becomes simple pointer arithmetic (very fast!)

    • Don't trim whitespace if you don't need to (use PRESERVE BLANKS.)

  3. Make conversions efficient. There are several conversions that the loader does for you, character set conversion and datatype conversions. Of course, the quickest conversion is no conversion.

    • Avoid character set conversions if you can. The loader supports four character sets: a) client character set (NLS_LANG of the client sqlldr process); b) datafile character set (usually the same as the client character set, but can be different); c) server character set; and d) server national character set. Performance is optimized if all character sets are the same. For direct path loads, it is best if the datafile character set and the server character set are the same. If the character sets are the same, character set conversion buffers are not allocated.

    • Use single byte character sets if you can.

  4. Use direct path loads.

  5. Use "sorted indexes" clause.

  6. Avoid unnecessary NULLIF and DEFAULTIF clauses. Each clause must be evaluated on each column which has a clause associated with it for EVERY row loaded.

  7. Use parallel direct path loads and parallel index create when you can.


Copyright © 1999 Oracle Corporation.

All Rights Reserved.