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 -> Re: Cost of TO_DATE

Re: Cost of TO_DATE

From: Morten Primdahl <morten_at_caput.com>
Date: 2000/04/04
Message-ID: <38E9BAE2.C6AA5ECB@caput.com>#1/1

Thanks Sybrand, just the info I needed. Where can I find more information on how Oracle performs with indexes, various datatypes etc? Eg. I need to find out what's fastest, letting an INSERT fail on a unique column or check for contents of the table first and then insert the missing rows. I'd like to learn more about stuff like that in general, any particular reference?

Thanks

Morten

Sybrand Bakker wrote:
>
> <primdahl_at_my-deja.com> wrote in message news:8c0at2$icn$1_at_nnrp1.deja.com...
> >
> >
> > Hi. Any of you know how TO_DATE performs? I
> > want to be able to search out a set of data,
> > restricted by date fields.
> >
> > SELECT * FROM table
> > WHERE date_A < TO_DATE(..)
> > AND date_B > TO_DATE(..)
> >
> > I guess Oracle only performs the TO_DATE
> > operation once and uses the same value
> > in all comparisons. What kind of index can
> > I make on date fields? If I want top
> > performance, would I be better off
> > implementing dates as a tuple of
> > day int(2), month int(2), year int(4)
> > or?
> >
> > Tips'n'tricks appreciated, thanks :)
> >
> > Morten
> >
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
>
> The to_date operation is performed only once and not costly.
> You should implement dates preferably as dates.
> Oracle doesn't know int2 and int4 (will be implemented as number(38)) so
> that would make things worse.
> You can create the normal indexes on date_fields, and sometimes you will
> need to resort to trics to avoid a to_char on an indexed date column, as any
> function on the left hand side will force the index not to be used. In
> Oracle 8i however you can create function based indexes.
>
> Hth,
> Sybrand Bakker, Oracle DBA
 

-- 
Morten Primdahl		Caput ApS	Tel +45 33 12 24 42		
morten_at_caput.com	Nygade 6	Fax +45 33 91 24 42
http://www.caput.com	DK-1164 Kbh K
Received on Tue Apr 04 2000 - 00:00:00 CDT

Original text of this message

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