How about loading to a temp table where that col is varchar, then use a SQL
to insert those records into the real table while doing the substr?
"Carl Mercer" <cmercer_at_vibrant-1.com> 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
Received on Wed Feb 06 2002 - 10:02:24 CST
