Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> date format question

date format question

From: Carl Mercer <cmercer_at_vibrant-1.com>
Date: 6 Feb 2002 07:26:05 -0800
Message-ID: <d0bb6654.0202060726.442dcfca@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 - 09:26:05 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US