Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> date format question
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 - 09:26:05 CST