I was experimenting between direct path loading and conventional loading abilities of Oracle SQL Loader and whoop, what a difference direct path loading makes..It loaded 23K odd rows into a table in Oracle in less than 1 second..
Amazing, the speed of loading data in direct path loading is incredibly fast..my logs below
SQL*Loader: Release 10.2.0.1.0 - Production on Wed Feb 23 11:24:01 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Control File: load.ctl
Data File: C:\load.csv
Bad File: load.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 SOURCE_DATA, loaded from every logical record.
Insert option in effect for this table: INSERT
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
INPUT_DATA FIRST * , O(") CHARACTER
Table SOURCE_DATA:
23000 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.
Column array rows : 5000
Stream buffer bytes: 256000
Read buffer bytes: 1048576
Total logical records skipped: 0
Total logical records read: 23000
Total logical records rejected: 0
Total logical records discarded: 0
Total stream buffers loaded by SQL*Loader main thread: 6
Total stream buffers loaded by SQL*Loader load thread: 0
Run began on Wed Feb 23 11:24:01 2011
Run ended on Wed Feb 23 11:24:01 2011
Elapsed time was: 00:00:00.36
CPU time was: 00:00:00.14
For comparison, the same with conventional loading was nearly three times as this one..
Sample control file:
load data
infile 'C:\load.csv'
into table source_data
fields terminated by "," optionally enclosed by '"'
(input_data)
No comments:
Post a Comment