| clob infile taking long time to load few records [message #340089] |
Mon, 11 August 2008 07:30 |
manoj339
Messages: 9
Registered: April 2007
|
Junior Member |
|
|
Hi,
The control file and log file is mentioned below.It is taking 20 min to load 900 kb file containing infile clob data.
The length of clob can be variable length.
I have explicitly mentioned as 2000000000 .However clob data as per 9i can be upto 4GB.How can I change ctl file or sqlloader parameters to improve performance of this load.
sqlldr conn_details control="psd_term_20080811.ctl" data="psd_term_20080811_L.dat" log="psd_term_20080811_load_L.log" bad="psd_term_20080811_L.bad" discard="psd_term_20080811_L.dsc" rows=500 errors=5000000
control file of sqlloader :
LOAD DATA
INFILE '' "str X'3C213E0A'"
APPEND INTO TABLE PSD_TERM
FIELDS TERMINATED BY '~^'
TRAILING NULLCOLS
(
"PSD_ID" CHAR(16) NULLIF ("PSD_ID"=BLANKS)
,"PSD_SERIAL_NUM" CHAR(4) NULLIF ("PSD_SERIAL_NUM"=BLANKS)
,"PSD_TERM_COD" CHAR(4) NULLIF ("PSD_TERM_COD"=BLANKS)
,"PSD_TERM_SER_NO" CHAR(4) NULLIF ("PSD_TERM_SER_NO"=BLANKS)
,"VERSION_DT" DATE "DD-MON-YYYY HH:MI:SS AM" NULLIF ("VERSION_DT"=BLANKS)
,"LATEST_VERSION" CHAR(1) NULLIF ("LATEST_VERSION"=BLANKS)
,"NARRATIVE" CHAR(2000000000)
,"PARTITION_DT" DATE "DD-MON-YYYY HH:MI:SS AM" NULLIF ("PARTITION_DT"=BLANKS)
,"NARRATIVE_UNEXPANDED" CHAR(2000000000)
)
log file o/p of sqlloader
SQL*Loader: Release 9.2.0.8.0 - Production on Fri Aug 8 08:47:12 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Control File: /imaticaroot/iwhtrd/SessLogs/psd_term_20080808.ctl
Data File: /imaticaroot/iwhtrd/SessLogs/psd_term_20080808_L.dat
File processing option string: "str X'3C213E0A'"
Bad File: /imaticaroot/iwhtrd/SessLogs/psd_term_20080808_L.bad
Discard File: /imaticaroot/iwhtrd/SessLogs/psd_term_20080808_L.dsc
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 5000000
Bind array: 500 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional
Silent options: FEEDBACK
Table "IWH_OWNER"."PSD_TERM", loaded from every logical record.
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
"PSD_ID" FIRST 16 CHARACTER
Terminator string : '~^'
NULL if "PSD_ID" = BLANKS
"PSD_SERIAL_NUM" NEXT 4 CHARACTER
Terminator string : '~^'
NULL if "PSD_SERIAL_NUM" = BLANKS
"PSD_TERM_COD" NEXT 4 CHARACTER
Terminator string : '~^'
NULL if "PSD_TERM_COD" = BLANKS
"PSD_TERM_SER_NO" NEXT 4 CHARACTER
Terminator string : '~^'
NULL if "PSD_TERM_SER_NO" = BLANKS
"VERSION_DT" NEXT * DATE DD-MON-YYYY HH:MI:SS AM
Terminator string : '~^'
NULL if "VERSION_DT" = BLANKS
"LATEST_VERSION" NEXT 1 CHARACTER
Terminator string : '~^'
NULL if "LATEST_VERSION" = BLANKS
"NARRATIVE" NEXT ***** CHARACTER
Maximum field length is 2000000000
Terminator string : '~^'
"PARTITION_DT" NEXT * DATE DD-MON-YYYY HH:MI:SS AM
Terminator string : '~^'
NULL if "PARTITION_DT" = BLANKS
"NARRATIVE_UNEXPANDED" NEXT ***** CHARACTER
Maximum field length is 2000000000
Terminator string : '~^'
Table "PSD_TERM":
177 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.
Space allocated for bind array: 4000000564 bytes(1 rows)
Read buffer bytes: 1048576
Total logical records skipped: 0
Total logical records read: 178
Total logical records rejected: 0
Total logical records discarded: 0
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
