Oracle7 Server Utilities

Parallel Data Loading

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

Restrictions

To load a table in parallel, the table must not be indexed.

You can only use parallel load to append rows. REPLACE, TRUNCATE, and INSERT should not be used. If you must truncate a table before a parallel load, you must do it manually.

**Initiating Multiple SQL*Loader Sessions**

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

PARALLEL can be specified on the command line or in a parameter file. It can also be specified in the control file with the OPTIONS clause.

For example, to invoke three SQL*Loader direct path load sessions on the same table, you would execute the following commands at the operating system prompt:

SQLLOAD USERID=SCOTT/TIGER CONTROL=LOAD1.CTL DIRECT=TRUE PARALLEL=TRUE
SQLLOAD USERID=SCOTT/TIGER CONTROL=LOAD2.CTL DIRECT=TRUE PARALLEL=TRUE
SQLLOAD USERID=SCOTT/TIGER CONTROL=LOAD3.CTL DIRECT=TRUE PARALLEL=TRUE

The previous commands must be executed in separate sessions, or if permitted on your operating system, as separate background jobs. Note the use of multiple control files. This allows you to be flexible in specifying the files to use for the direct path load (see the example of one of the control files below).

Note: Indexes are not created during a parallel load. Any indexes must be created manually after the load completes. You can use the parallel index creation feature to speed the creation 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 table in the database above the table's high water mark. The last extent used for each loader session is trimmed of any free space before being combined with the other extents of the SQL*Loader session.

Options Keyword

It is recommended that each concurrent session use files located on different disks to allow for the maximum I/O throughput. You can specify the filename of any valid datafile in the table's tablespace with the FILE keyword of the OPTIONS clause. The following example illustrates a portion of one of the control files used for the SQL*Loader sessions in the previous example:

LOAD DATA
INFILE 'load1.dat'
INSERT INTO TABLE emp
OPTIONS(FILE='/dat/data1.dat')
(empno POSITION(01:04) INTEGER EXTERNAL NULLIF empno=BLANKS
...

Note: The FILE keyword is specified in a table-specific OPTIONS clause. That is, the OPTIONS clause is placed within an INTO TABLE statement, rather than before.

Specifying Temporary Segments

You can specify the database file from which the temporary segments are allocated with the FILE keyword in the OPTIONS clause for each table 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 tables being loaded with that session.

Enabling Constraints After A Parallel Direct Path Load

If REENABLE is set to TRUE, each SQL*Loader session attempts to re-enable constraints on a table after a direct path load. Each SQL*Loader session has a share lock on the table, however, so that another session cannot enable constraints before it is a finished. When the last session completes, its attempt to re-enable constraints succeeds.

Warning: There is a danger that some constraints may not be re-enabled after a direct path load, you should check the status of the constraint after completing the load to ensure that it was enabled properly.

PRIMARY and UNIQUE KEY constraints

PRIMARY KEY and UNIQUE key constraints create indexes on a table when they are enabled, and subsequently can take a significantly long time to enable after a direct path loading session if the table is very large.

You should consider enabling these constraints manually after a load (and not specify the automatic enable feature). This allows you to manually create the required indexes in parallel to save time before enabling the constraint. See the Oracle7 Server Administrator's Guide for more information about creating indexes in parallel.