0373-0376 | Oracle Development Unleashed (3rd Edition)

Previous Table of Contents Next

Page 373

Figure 16.9.
Striping parallel loads.

You must drop indexes and disable primary key constraints and unique key constraints on the table before doing a parallel load. Oracle version 7.1 introduced parallel index creation, but you may use it only with a CREATE INDEX statement, not within an ALTER TABLE CREATE CONSTRAINT statement. Therefore, the best method is to create the indexes first, using the parallel option, and then create the primary key and unique constraints, which use the previously created indexes.

NOTE

Parallel SQL*Loader sessions that are interrupted normally (for example, by a Ctrl+C in UNIX) terminate normally; they stop processing where they were interrupted . Any temporary segments created during the session are added to the table's high water mark. Any parallel session that was " killed " from an O/S command terminates abnormally; the temporary segments are dropped and not added to the table's high water mark.

TIP

If you are creating a large table (which you know will be populated by the parallel option of SQL*Loader), first create a striped tablespace to hold the table, equally sizing the data files that comprise the tablespace across multiple devices. Next, create the table with the
 continues 

Page 374

continued
minimal initial extent size (one Oracle block) and the NEXT extent sized to fully populate the size of the stripes , less one Oracle block. Be sure to set PCTINCREASE to 0. Next, you must analyze the data being loaded to determine the size of each of the input data files you want to load. You want to size the input files so that SQL*Loader fully populates (as much as possible) the striped tablespace files to minimize wasted space.

Your goal is to have concurrent SQL*Loader sessions each process an input file and place the output in a separate tablespace file, thereby maximizing throughput. Each SQL*Loader session creates one temporary segment in its specified tablespace file, with the extent size being the size of the data file. When the session completes, SQL*Loader trims the excess space from the temporary extent (because it has only one extent) and adds it above the table's high water mark.

Using this method, you evenly distribute the data across devices, thereby maximizing database I/O, minimizing the number of extents for the table, and maximizing SQL*Loader performance.

Control File Syntax

The control file is in free format; it does, however, have a few rules:

White space, carriage returns, tabs, and so on are allowed at any position in the file.
As in Oracle, the contents of the file are case insensitive except for quoted (single or double) strings.
You can include comments anywhere , except within the data ( assuming the data is contained in the control file), by prefacing them with two hyphens; SQL*Loader ignores anything from the double hyphen to the end of the line.
You can use reserved words for table or column names ; the words, however, must be enclosed in double quotation marks.

TIP

It is generally good practice not to use Oracle reserved words for any database object names. I have seen numerous cases in which Oracle got confused when reserved words were used as database object names. (The error messages are ambiguous.) For the complete lists of reserved words for SQL*Loader, SQL*DBA, and Oracle, refer to the Oracle8 Server Utilities Guide and the SQL Language Reference Manual.

Page 375

The control file is logically organized into seven sections:

OPTIONS clause
UNRECOVERABLE/RECOVERABLE clause
LOAD DATA clause
INFILE clause
Table loading method
CONCATENATION clause
INTO TABLE clause

Listings 16.1, 16.2, and 16.3 help illustrate some of the syntax described in this section.

Listing 16.1 is a sample control file for a direct path parallel load using a fixed-format input file. This control file was used to test SQL*Loader results using the direct path parallel option in an unrecoverable mode. For illustration purposes only, the SQL*Loader functions CONSTANT, SEQUENCE, SYSDATE , and RECNUM were used to populate column data.

Listing 16.1. Parallel load control file.

 OPTIONS (direct=true, parallel=true) UNRECOVERABLE LOAD DATA INFILE'/data01/ORACLE/TEST/fixed_data.dat' BADFILE'fixed_data.bad' DISCARDFILE'fixed_data.dis' INSERT INTO TABLE loader_test OPTIONS (FILE='data02/ORACLE/TEST/test1_TEST_01.dbf') (loader_constant CONSTANT "A" loader_sequence sequence (1,1), loader_sysdate sysdate, loader_recnum recnum, loader_desc POSITION (01:30) char loader_col1 POSITION (31:40) char, loader_col2 POSITION (41:50) char, loader_col3 POSITION (51:60) char, loader_col4 POSITION (61:70) char)

Listing 16.2 is a sample control file for a conventional path load using a variable-format file.

Listing 16.2. Variable-format control file.

 LOAD DATA INFILE'data04/ORACLE/TEST/prod' BADFILE'prod.bad' INSERT INTO TABLE prod' FIELDS TERMINATED BY','OPTIONALLY ENCLOSED BY "" trailing nullcols   (

 continues

Page 376

Listing 16.2. continued

 PROD_CODE,      PROD_DESCR,      PROD_CLASS,      PROD MKTG_CODE

terminated by whitespace)

Listing 16.3 is a sample control file for loading a binary EBCDIC file into a USASCII database using the conventional path. Note that the file is also using packed decimal fields.

Listing 16.3. Foreign character set control file.

 LOAD DATA CHARACTERSET WE8EBCDIC500 INFILE `data01/ORACLE/TEST/prod_detail'"FIX 28" INSERT INTO TABLE prod_detail    (     PROD_CODE position(01:04) char,     SALES_MTD_LY position(05:10) decimal(11,2),     SALES_YTD_LY position(11:16) decimal(11,2),     SALES_MTD position(17:22) decimal(11,2),     SALES_YTD position(23:28) decimal(11,2)    )

OPTIONS Clause

The OPTIONS clause, which enables you to specify some of the runtime arguments in the control file rather than on the command line, is optional. This clause is particularly useful if the length of the command line exceeds the maximum command-line length for your operating system. The arguments you can include in the OPTIONS clause are described in Table 16.1. Even if you specify the arguments in the OPTIONS clause, the command-line specifications override them.

Table 16.1. Control file OPTIONS clause arguments.

Argument	Description
SKIP = n	Logical records to skip
LOAD = n	Logical records to load (default all)
ERRORS = n	Errors to allow before termination
ROWS = n	Rows in bind array (conventional); rows between saves (direct)
BINDSIZE = n	Size of bind array in bytes
SILENT = {HEADERFEEDBACK ERRORDISCARDSALL}	Messages to suppress
DIRECT = {TRUEFALSE}	Load path method
PARALLEL = {TRUEFALSE}	Multiple concurrent sessions

Previous Table of Contents Next