| Question about sql*loader: [message #93246] |
Thu, 12 February 2004 07:22  |
Nauj
Messages: 33
Registered: February 2004
|
Member |
|
|
I have to do a serie of load processes to Oracle tables. The data is in flat files and I'm going to use the sql*loader utility to load data into database.
I'm interested in the DIRECT_PATH option because the load time is important in this processes. I know the with DIRECT_PATH = YES sql*loader is faster loading because in this case it dosen't makes some validations.
For example: validations abouts blocks space, constraints are disabled, it doesn't write in Oracle redolog and more...
The load time is important, but also query time. I don't if this kind of load have an influence in the disposition of the data and in the time of resolving queries.
With DIRECT_PATH = YES ...will be the queries of the load data lower???
Any information about this option will be good. Thanks in advance.
|
|
|
|
| Re: Question about sql*loader: [message #93349 is a reply to message #93246] |
Sun, 08 August 2004 08:47  |
Venkata Phani Kumar.Velur
Messages: 6
Registered: August 2004
|
Junior Member |
|
|
Hello,
just to let you know, Inspite of using this Loader why dont u use UTL packages...this is more fater than anyany loader. As you said that the file is pure flat files. I guess that the right choice ...Rest is upto u. If u need any thing on UTL's let me know ....I shall give u some examples which i've done. They are currenlty running in production.
I've extracted something for u on SQL*LOADER...
Cheers
Ven (Venkat)
When to Use a Direct Path Load
If none of the above restrictions apply, you should use a direct path load when:
* you have a large amount of data to load quickly. A direct path load can quickly load and index large amounts of data. It can also load data into either an empty or non-empty table,
* you want to load data in PARALLEL for maximum performance. See Parallel Data Loading Models.
* you want to load data in a character set that cannot be supported in your current session, or when the conventional conversion to the database character set would cause errors.
Restrictions on Using Direct Path Loads
In addition to the general load conditions described in Conventional Path Load versus Direct Path Load, the following conditions must be satisfied to use the direct path load method:
* Tables are not clustered.
* Tables to be loaded do not have any active transactions pending.
To check for this condition, use the Enterprise Manager command MONITOR TABLE to find the object ID for the table(s) you want to load. Then use the command MONITOR LOCK to see if there are any locks on the table.
* You cannot have SQL strings in the control file.
Note also that the following features are not available with direct path load.
* loading object columns
* loading LOBs
* loading VARRAYs
* loading nested tables
* specifying OIDs for object tables with system-generated OIDs
* specifying SIDs
* loading REF columns
* loading BFILE columns
* physical records (set by the command-line option READSIZE) larger than 64k
Restrictions on a Direct Path Load of a Single Partition
In addition to the above listed restrictions, loading a single partition has the following restrictions:
* The table which the partition is a member of cannot have any global indexes defined on it.
* Enabled referential and check constraints on the table which the partition is a member of are not allowed.
* Enabled triggers are not allowed.
Integrity Constraints
All integrity constraints are enforced during direct path loads, although not necessarily at the same time. NOT NULL constraints are enforced during the load. Records that fail these constraints are rejected.
UNIQUE constraints are enforced both during and after the load. A record which violates a UNIQUE constraint is not rejected (the record is not available in memory when the constraint violation is detected.)
Integrity constraints that depend on other rows or tables, such as referential constraints, are disabled before the direct path load and must be re-enabled afterwards. If REENABLE is specified, SQL*Loader can re-enable them automatically at the end of the load. When the constraints are re-enabled, the entire table is checked. Any rows that fail this check are reported in the specified error log. See the section in this chapter calledDirect Loads, Integrity Constraints, and Triggers .
Field Defaults on the Direct Path
DEFAULT column specifications defined in the database are not available when loading on the direct path. Fields for which default values are desired must be specified with the DEFAULTIF clause, described on DEFAULTIF Clause. If a DEFAULTIF clause is not specified, and the field is NULL, then a NULL value is inserted into the database.
Control File
The control file is ULCASE6.CTL.
LOAD DATA
INFILE 'ulcase6.dat'
INSERT
INTO TABLE emp
1) SORTED INDEXES (empix)
2) (empno POSITION(01:04) INTEGER EXTERNAL NULLIF empno=BLANKS,
ename POSITION(06:15) CHAR,
job POSITION(17:25) CHAR,
mgr POSITION(27:30) INTEGER EXTERNAL NULLIF mgr=BLANKS,
sal POSITION(32:39) DECIMAL EXTERNAL NULLIF sal=BLANKS,
comm POSITION(41:48) DECIMAL EXTERNAL NULLIF comm=BLANKS,
deptno POSITION(50:51) INTEGER EXTERNAL NULLIF deptno=BLANKS)
Notes:
1. 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.
2. 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:
sqlldr scott/tiger ulcase6.ctl direct=true log=ulcase6.log
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
