Case 6: Loading Using the Direct Path Load Method
This case study loads the EMP table using the direct path load method and concurrently builds all indexes. It illustrates the following functions:
Use of the direct path load method to load and index data. See Chapter 8, "SQL*Loader: Conventional and Direct Path Loads".
How to specify the indexes for which the data is pre-sorted. See Pre-sorting Data for Faster Indexing.
Loading all-blank numeric fields as null. See Loading All-Blank Fields.
The NULLIF clause. See NULLIF Keyword.
Note: Specify the name of the table into which you want to load data; otherwise, you will see LDR-927. Specifying DIRECT=TRUE as a command-line parameter is not an option when loading into a synonym for a table.
In this example, field positions and datatypes are specified explicitly.
The SORTED INDEXES clause identifies indexes:presorting data:case study the indexes on which the data is sorted. This clause indicates that the datafile is sorted on the columns in the EMPIX index. This clause allows SQL*Loader to optimize index creation by eliminating the sort phase for this data when using the direct path load method.
The NULLIF...BLANKS clause specifies that the column should be loaded as NULL if the field in the datafile consists of all blanks. For more information, refer to Loading All-Blank Fields.
Invoking SQL*Loader
Run the script ULCASE6.SQL as SCOTT/TIGER then enter the following at the command line:
Additional Information: The command "sqlldr" is a UNIX-specific invocation. To invoke SQL*Loader on your operating system, see your Oracle operating system-specific documentation.
Log File
The following is a portion of the log file:
Control File: ulcase6.ctl
Data File: ulcase6.dat
Bad File: ulcase6.bad
Discard File: none specified
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation: none specified
Path used: Direct
Table EMP, loaded from every logical record.
Insert option in effect for this table: REPLACE
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
EMPNO 1:4 4 CHARACTER
ENAME 6:15 10 CHARACTER
JOB 17:25 9 CHARACTER
MGR 27:30 4 CHARACTER
NULL if MGR = BLANKS
SAL 32:39 8 CHARACTER
NULL if SAL = BLANKS
COMM 41:48 8 CHARACTER
NULL if COMM = BLANKS
DEPTNO 50:51 2 CHARACTER
NULL if EMPNO = BLANKS
The following index(es) on table EMP were processed:
index SCOTT.EMPIX loaded successfully with 7 keys
Table EMP:
7 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Bind array size not used in direct path.
Space allocated for memory besides bind array: 0 bytes
Total logical records skipped: 0
Total logical records read: 7
Total logical records rejected: 0
Total logical records discarded: 0
Run began on Sun Nov 08 11:15:28 1998
Run ended on Sun Nov 08 11:15:31 1998
Elapsed time was: 00:00:03.22
CPU time was: 00:00:00.10
ORA-02352 Direct path connection must be homogeneous
Cause: The user-side to kernel-side connection is heterogeneous.
Action: Make the connection between identical hardware and operating systems.