SQL Loader - Direct Path Loading-Example

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 - 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           

  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 '"'                              

No comments:

Post a Comment