Conventional Path Loads, Direct Path Loads, and External Table Loads

7.9 Conventional Path Loads, Direct Path Loads, and External Table Loads

SQL*Loader provides several methods to load data.

Conventional Path Loads
During conventional path loads, the input records are parsed according to the field specifications, and each data field is copied to its corresponding bind array (an area in memory where SQL*Loader stores data to be loaded).
Direct Path Loads
A direct path load parses the input records according to the field specifications, converts the input field data to the column data type, and builds a column array.
Parallel Direct Path
A parallel direct path load allows multiple direct path load sessions to concurrently load the same data segments (allows intrasegment parallelism).
External Table Loads
External tables are defined as tables that do not reside in the database, and can be in any format for which an access driver is provided.
Choosing External Tables Versus SQL*Loader
Learn which method can provide the best load performance for your data load situations.
Behavior Differences Between SQL*Loader and External Tables
Oracle recommends that you review the differences between loading data with external tables, using the ORACLE_LOADER access driver, and loading data with SQL*Loader conventional and direct path loads.
Loading Tables Using Data Stored into Object Storage
Learn how to load your data from Object Storage into standard Oracle Database tables using SQL*Loader.

Parent topic: Understanding How to Use SQL*Loader

7.9.1 Conventional Path Loads

During conventional path loads, the input records are parsed according to the field specifications, and each data field is copied to its corresponding bind array (an area in memory where SQL*Loader stores data to be loaded).

When the bind array is full (or no more data is left to read), an array insert operation is performed.

SQL*Loader stores LOB fields after a bind array insert is done. Thus, if there are any errors in processing the LOB field (for example, the LOBFILE could not be found), then the LOB field is left empty. Note also that because LOB data is loaded after the array insert has been performed, BEFORE and AFTER row triggers may not work as expected for LOB columns. This is because the triggers fire before SQL*Loader has a chance to load the LOB contents into the column. For instance, suppose you are loading a LOB column, C1, with data and you want a BEFORE row trigger to examine the contents of this LOB column and derive a value to be loaded for some other column, C2, based on its examination. This is not possible because the LOB contents will not have been loaded at the time the trigger fires.

7.9.2 Direct Path Loads

A direct path load parses the input records according to the field specifications, converts the input field data to the column data type, and builds a column array.

The column array is passed to a block formatter, which creates data blocks in Oracle database block format. The newly formatted database blocks are written directly to the database, bypassing much of the data processing that normally takes place. Direct path load is much faster than conventional path load, but entails several restrictions.

Parent topic: Conventional Path Loads, Direct Path Loads, and External Table Loads

7.9.3 Parallel Direct Path

A parallel direct path load allows multiple direct path load sessions to concurrently load the same data segments (allows intrasegment parallelism).

Parallel direct path is more restrictive than direct path.

7.9.4 External Table Loads

External tables are defined as tables that do not reside in the database, and can be in any format for which an access driver is provided.

Oracle Database provides two access drivers: ORACLE_LOADER, and ORACLE_DATAPUMP. By providing the database with metadata describing an external table, the database is able to expose the data in the external table as if it were data residing in a regular database table.

An external table load creates an external table for data that is contained in an external data file. The load runs INSERT statements to insert the data from the data file into the target table.

The advantages of using external table loads over conventional path and direct path loads are as follows:

If a data file is big enough, then an external table load attempts to load that file in parallel.
An external table load allows modification of the data being loaded by using SQL functions and PL/SQL functions as part of the INSERT statement that is used to create the external table.

Note:

An external table load is not supported using a named pipe on Windows operating systems.

7.9.5 Choosing External Tables Versus SQL*Loader

Learn which method can provide the best load performance for your data load situations.

The record parsing of external tables and SQL*Loader is very similar, so normally there is not a major performance difference for the same record format. However, due to the different architecture of external tables and SQL*Loader, there are situations in which one method may be more appropriate than the other.

Use external tables for the best load performance in the following situations:

You want to transform the data as it is being loaded into the database
You want to use transparent parallel processing without having to split the external data first

Use SQL*Loader for the best load performance in the following situations:

You want to load data remotely
Transformations are not required on the data, and the data does not need to be loaded in parallel
You want to load data, and additional indexing of the staging table is required

Parent topic: Conventional Path Loads, Direct Path Loads, and External Table Loads

7.9.6 Behavior Differences Between SQL*Loader and External Tables

Oracle recommends that you review the differences between loading data with external tables, using the ORACLE_LOADER access driver, and loading data with SQL*Loader conventional and direct path loads.

The information in this section does not apply to the ORACLE_DATAPUMP access driver.

Multiple Primary Input Data Files
If there are multiple primary input data files with SQL*Loader loads, then a bad file and a discard file are created for each input data file.
Syntax and Data Types
With external table loads, you cannot use SQL*Loader to load unsupported syntax and data types.
Byte-Order Marks
With SQL*Loader, whether the byte-order mark is written depends on the character set or on the table load.
Default Character Sets, Date Masks, and Decimal Separator
The display of NLS character sets are controlled by different settings for SQL*Loader and external tables.
Use of the Backslash Escape Character
SQL*Loader and external tables use different conventions to identify single quotation marks as an enclosure character.

Parent topic: Conventional Path Loads, Direct Path Loads, and External Table Loads

7.9.6.1 Multiple Primary Input Data Files

If there are multiple primary input data files with SQL*Loader loads, then a bad file and a discard file are created for each input data file.

With external table loads, there is only one bad file and one discard file for all input data files. If parallel access drivers are used for the external table load, then each access driver has its own bad file and discard file.

Parent topic: Behavior Differences Between SQL*Loader and External Tables

7.9.6.2 Syntax and Data Types

With external table loads, you cannot use SQL*Loader to load unsupported syntax and data types.

As part of your data migration plan, do not attempt to use SQL*Loader with unsupported syntax or data types. Resolve issues before your migration. You cannot use the following syntax or data types:

Use of CONTINUEIF or CONCATENATE to combine multiple physical records into a single logical record.
Loading of the following SQL*Loader data types: GRAPHIC, GRAPHIC EXTERNAL, and VARGRAPHIC
Use of the following database column types: LONG, nested table, VARRAY, REF, primary key REF, and SID

Note:

All forms of LONG data types (LONG, LONG RAW, LONG VARCHAR, LONG VARRAW) were deprecated in Oracle8i Release 8.1.6. For succeeding releases, the LONG data type was provided for backward compatibility with existing applications. In new applications developed with later releases, Oracle strongly recommends that you use CLOB and NCLOB data types for large amounts of character data.

Parent topic: Behavior Differences Between SQL*Loader and External Tables

7.9.6.3 Byte-Order Marks

With SQL*Loader, whether the byte-order mark is written depends on the character set or on the table load.

If a primary data file uses a Unicode character set (UTF8 or UTF16), and it also contains a byte-order mark (BOM), then the byte-order mark is written at the beginning of the corresponding bad and discard files.

With external table loads, the byte-order mark is not written at the beginning of the bad and discard files.

Parent topic: Behavior Differences Between SQL*Loader and External Tables

7.9.6.4 Default Character Sets, Date Masks, and Decimal Separator

The display of NLS character sets are controlled by different settings for SQL*Loader and external tables.

With SQL*Loader, the default character set, date mask, and decimal separator are determined by the settings of NLS environment variables on the client.

For fields in external tables, the database settings of the NLS parameters determine the default character set, date masks, and decimal separator.

Parent topic: Behavior Differences Between SQL*Loader and External Tables

7.9.6.5 Use of the Backslash Escape Character

SQL*Loader and external tables use different conventions to identify single quotation marks as an enclosure character.

With SQL*Loader, to identify a single quotation mark as the enclosure character, you can use the backslash (\) escape character. For example

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\''

In external tables, the use of the backslash escape character within a string raises an error. The workaround is to use double quotation marks to identify a single quotation mark as the enclosure character. For example:

TERMINATED BY ',' ENCLOSED BY "'"

Parent topic: Behavior Differences Between SQL*Loader and External Tables

7.9.7 Loading Tables Using Data Stored into Object Storage

Learn how to load your data from Object Storage into standard Oracle Database tables using SQL*Loader.

Starting with Oracle Database 21c, you can use the SQL*Loader parameter CREDENTIAL to provide credentials to enable read access to object stores. Parallel loading from the object store is supported.

For a data file, you can specify the URI for the data file that you want to read on the object store. The CREDENTIAL values specify credentials granted to the user running SQL*Loader. These permissions enable SQL*Loader to access the object.

Note:

Mixing local files with object store files is not supported.

In the following example, you have a table (T) into which you are loading data:

SQL> create table t (x int, y int);

You have a data file that you want to load to this table, named file1.txt. The contents are as follows:

X,Y
1,2
4,5

To load this table into an object store, complete the following procedure:

Install the libraries required to enable object store input/output (I/O):
```
% cd $ORACLE_HOME/rdbms/lib
% make -f ins_rdbms.mk opc_on
```
Upload the file file1.txt to the bucket in Object Storage.

The easiest way to upload file to object storage is to upload the file from the Oracle Cloud console:
1. Open the Oracle Cloud console.
2. Select the Object Storage tile.
3. If not already created, create a bucket.
4. Click Upload, and select the file file1.txt to upload it into the bucket.
In Oracle Database, create the wallet and the credentials.

For example:
```
$ orapki wallet create -wallet /home/oracle/wallets  -pwd mypassword-auto_login
$ mkstore -wrl /home/oracle/wallets -createEntry oracle.sqlldr.credential.myfedcredential.username oracleidentitycloudservice/myuseracct@example.com
$ mkstore -wrl /home/oracle/wallets -createEntry oracle.sqlldr.credential.myfedcredential.password "MhAVCDfW+-ReskK4:Ho-zH"  
```
This example shows the use of a federated user account (myfedcredential). The password is automatically generated, as described in Oracle Cloud Infrastructure Documentation. "Managing Credentials," in the section "To create an auth token."

Note:

The mkstore wallet management command line tool is deprecated with Oracle Database 23ai, and can be removed in a future release.

To manage wallets, Oracle recommends that you use the orapki command line tool.

After creating the wallet, add the location in the sqlnet.ora file in the directory $ORACLE_HOME/network/admin directory.

For example:

vi test.ctl
LOAD DATA
INFILE  'https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/dbcloudoci/b/myobjectstore/o/file1.txt'
truncate
INTO TABLE T
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(X,Y)

Run SQL*Loader to load the data into the object store.

For example:

sqlldr test/mypassword@pdb1 /home/oracle/test.ctl
credential=myfedcredentiallog=test.log  external_table=not_used