Home » RDBMS Server » Server Utilities » Tunning of SQLLDR control file (Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit)
Tunning of SQLLDR control file [message #438364] Fri, 08 January 2010 06:00 Go to next message
tapaskmanna
Messages: 98
Registered: January 2007
Location: Cyprus,Nicosia
Member
Hi,

I am using SQL Loader , to load the Single Data File ( contains 5000 Records aproximately) into 200 Tables.

Its take 00:35:49.39 time to load.

Query:
i) Is it possible to tune the control file to reduce the loading time
ii) For each condition data is traversing through all the records (i.e 5000) every time.
Is it possible to skip?


Please suggest


Control File:
LOAD DATA
INFILE 'D:\loader\ULY02.01070839.BTM'
APPEND

INTO TABLE T1
WHEN TABCODE_ID = '1'
FIELDS TERMINATED BY '|' TRAILING NULLCOLS
(
TABCODE_ID FILLER POSITION (1) INTEGER EXTERNAL,
F1 INTEGER EXTERNAL,
F2 CHAR,
F3 INTEGER EXTERNAL,
F4 DATE "DD-MON-YYYY HH24:MI:SS",
)

INTO TABLE T1
WHEN TABCODE_ID = '2'
FIELDS TERMINATED BY '|' TRAILING NULLCOLS
(
TABCODE_ID FILLER POSITION (1) INTEGER EXTERNAL,
F1 INTEGER EXTERNAL,
F2 CHAR,
F3 INTEGER EXTERNAL,
F4 DATE "DD-MON-YYYY HH24:MI:SS",
)

INTO TABLE T1
WHEN TABCODE_ID = '3'
FIELDS TERMINATED BY '|' TRAILING NULLCOLS
(
TABCODE_ID FILLER POSITION (1) INTEGER EXTERNAL,
F1 INTEGER EXTERNAL,
F2 CHAR,
F3 INTEGER EXTERNAL,
F4 DATE "DD-MON-YYYY HH24:MI:SS",
)

 ....
 ....
 ....
 ....
 ....
 
 INTO TABLE T200
WHEN TABCODE_ID = '200'
FIELDS TERMINATED BY '|' TRAILING NULLCOLS
(
TABCODE_ID FILLER POSITION (1) INTEGER EXTERNAL,
F1 INTEGER EXTERNAL,
F2 CHAR,
F3 INTEGER EXTERNAL,
F4 DATE "DD-MON-YYYY HH24:MI:SS",
)



Log File:
Table T1:
  200 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  5000 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.


Table T2:
  500 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  5000 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.


Table T3:
  600 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  5000 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.


Table T4:
  0 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  5000 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.

Table T5:
  0 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  5000 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.


Table T6:
  0 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  5000 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.

Table T7:
  0 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  5000 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.

Table T8:
  0 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  5000 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.

...
...
...
...

Table T200:
  0 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  5000 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.


Space allocated for bind array:                 591594 bytes(1 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:          4944
Total logical records rejected:         0
Total logical records discarded:        0

Run began on Fri Jan 08 14:28:13 2010
Run ended on Fri Jan 08 15:04:03 2010

Elapsed time was:     00:35:49.39
CPU time was:         00:00:24.72





Re: Tunning of SQLLDR control file [message #438403 is a reply to message #438364] Fri, 08 January 2010 12:19 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I might be wrong, but smells like a horrible design. Why do you need 200 tables? What's wrong with only one table? What do you do with 200 tables, after you load all records? How do you manipulate those tables? Did you write 200 different (actually, the same) procedures that select (or whatever else) from those tables? Why
select f1, f2, f3, to_char(f4, 'dd.mm.yyyy hh24:mi:ss') f_4
from t1
where ...;

select f1, f2, f3, to_char(f4, 'dd.mm.yyyy hh24:mi:ss') f_4
from t3
where ...;

select f1, f2, f3, to_char(f4, 'dd.mm.yyyy hh24:mi:ss') f_4
from t3
where ...;

etc.
when you could have done that as
select f1, f2, f3, to_char(f4, 'dd.mm.yyyy hh24:mi:ss') f_4
from one_table
where id = <id you are interested in>
  and ...


Though, perhaps your design is OK; could you tell something more about it?
Re: Tunning of SQLLDR control file [message #438414 is a reply to message #438403] Fri, 08 January 2010 15:37 Go to previous message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
In addition to what Littlefoot said, you might consider a partitioned table. If you do need 200 tables, it might be quicker to use SQL*Loader to load them into a staging table, create an index on the id column, then insert the appropriate rows into the appropriate tables based on the id column.

Previous Topic: Problem with expdp through EM
Next Topic: export and import issue
Goto Forum:
  


Current Time: Sun Dec 11 08:10:49 CST 2016

Total time taken to generate the page: 0.09117 seconds