Oracle SQL*Loader Overview
SQL*Loader
is the primary method for quickly populating Oracle tables with data
from external files. It has a powerful data parsing engine that puts
little limitation on the format of the data in the datafile. SQL*Loader
is invoked when you specify the sqlldr command or use the Enterprise
Manager interface.
SQL*Loader is an integral feature of Oracle databases and is available in all configurations.
Key Features
SQL*Loader can be used to do the following:
- Load
data across a network. This means that a SQL*Loader client can be run
on a different system from the one that is running the SQL*Loader
server.
- Load data from multiple datafiles during the same load session
- Load data into multiple tables during the same load session
- Specify the character set of the data
- Selectively load data
- Load data from disk, tape, or named pipe
- Specify the character set of the data
- Generate sophisticated error reports, which greatly aid troubleshooting
- Load arbitrarily complex object-relational data
- Use either conventional or direct path loading.
File Types
SQL*Loader Control File
The
control file is a text file written in a language that SQL*Loader
understands. The control file tells SQL*Loader where to find the data,
how to parse and interpret the data, and where to insert the data.
Input Data and Datafiles
SQL*Loader
reads data from one or more files specified in the control file. From
SQL*Loader's perspective, the data in the datafile is organized as
records. A particular datafile can be in fixed record format, variable
record format, or stream record format. The chosen format depends on
the data and depends on the flexibility and performance necessary for
the job.
LOBFILEs
LOB
data can be lengthy enough that it makes sense to load it from a
LOBFILE. LOB data instances are still considered to be in fields, but
these fields are not organized into records. Therefore, the processing
overhead of dealing with records is avoided. This type of or
organization of data is ideal for LOB loading.
Bulk Loads
You can use SQL*Loader to bulk load objects, collections, and LOBs. SQL*Loader supports the following bulk loads:
- Two object types: column objects and row objects
- Load data from multiple datafiles during the same load session
- Two collection types: nested tables and VARRAYS
- Four LOB types: BLOBs, CLOBs, NCLOBs, and BFILEs.
Load Methods
SQL*Loader provides three methods to load data: Conventional Path, Direct Path, and External Table.
Conventional Path Load
Conventional
path load builds an array of rows to be inserted and uses the SQL
INSERT statement to load the data. During conventional path loads,
input records are parsed according to the field specifications, and
each data field is copied to its corresponding bind array. When the
bind array is full (or no more data is left to read), an array insert
is executed.
Direct Path Load
A
direct path load builds blocks of data in memory and saves these blocks
directly into the extents allocated for the table being loaded. A
direct path load uses the field specifications to build whole Oracle
blocks of data, and write the blocks directly to Oracle datafiles,
bypassing much of the data processing that normally takes place. Direct
path load is much faster than conventional load, but entails some
restrictions.
A
parallel direct path load allows multiple direct path load sessions to
concurrently load the same data segments. Parallel direct path is more
restrictive than direct path.
External Table Load
An
external table load creates an external table for data in a datafile
and executes INSERT statements to insert the data from the datafile
into the target table.
There are two advantages of using external table loads over conventional path and direct path loads:
- An
external table load attempts to load datafiles in parallel. If a
datafile is big enough, it will attempt 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.
Summary
SQL*Loader
is a high-speed data loading utility that loads data from external
files into tables in an Oracle database. It provides database
administrators with the fast performance and flexibility required to
get load jobs conducted as quickly and efficiently as possible.
Back to Oracle Database Utilities Page
|