Re: date format question

cmercer_at_vibrant-1.com (Carl Mercer) wrote in message news:<d0bb6654.0202060726.442dcfca_at_posting.google.com>...
> Solaris 2.6
> Oracle 9.0.1.2
> E450
>
> I have an input file from a sybase database. Right now I am dealing
> with 20000 rows, but in the near future I will be dealing with 10
> million rows.
>
> All the columns converted into Oracle data types well except for one,
> a date column.
>
> The input data (from the sybase instance) is in the format:
>
> MM/DD/YY HH24:MI:SS.###
>
> Where ### is sub second values. Right now, the input is all 000's but
> thats not always going to be the case.
>
> I am loading the data direct path sqlldr and if I edit the input file,
> I can get 5250 rows a second on a small oracle instance and an
> overloaded E450 (not parallel).
>
> If I leave the data in, I can still load it if I do something like
> this in the control file:
>
> ...
> loc_dttm char "to_date(substr(:loc_dttm,1, 17), 'MM/DD/YY
> HH24:MI:SS')",
> ...
>
> But this slows the direct path load to 995 rows a second.
>
>
> Is there any way to load the data into a date column without
> reformating it?
>
> If not, is there a way to load the data into a varchar and get the
> same date comparison functionality. Function based indexes probably
> will not work here because it would add a great deal of overhead to
> the data load.
>
>
> If not, is there a way to adjust the input data in the control file
> and not reduce the TPS?
>
>
> Thanks for any suggestions.
>
>
> Carl
> cmercer_at_vibrant-1.com

Carl,

I think you can try something like

LOAD DATA
INFILE *
TRUNCATE
INTO TABLE test
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(d date "MM/DD/RR HH24:MI:SS" terminated by '.',  v position(*+4))
BEGINDATA
01/01/01 12:22:34.111,lsjf
01/02/99 12:21:11.000,lskj

or maybe

LOAD DATA
INFILE *
TRUNCATE
INTO TABLE test
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(d date "MM/DD/RR HH24:MI:SS" terminated by '.',  f filler,
 v )
BEGINDATA
01/01/01 12:22:34.111,lsjf
01/02/99 12:21:11.000,lskj

BTW, you probably want to use 4 digit year if 'RR' is not enough.

Hth,
Mike Received on Wed Feb 06 2002 - 15:29:57 CST