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

Home -> Community -> Usenet -> c.d.o.misc -> Re: IS IT FASTER TO SELECT RECORDS BY CHARACTER OR BY DATE?

Re: IS IT FASTER TO SELECT RECORDS BY CHARACTER OR BY DATE?

From: Jim Kennedy <jim>
Date: Tue, 16 May 2006 20:36:47 -0700
Message-ID: <Z42dnfVyn9orBPfZnZ2dnUVZ_vidnZ2d@comcast.com>

"chrischee18" <chrischee18_at_gmail.com> wrote in message news:1147835931.121667.82690_at_i40g2000cwc.googlegroups.com...
> Suppose I have 2 tables. Both have exactly the same data. The only
> difference
> is just the data type for 'period' is different. Table A data type for
> 'period'
> is character. Table B data type for 'period' is date.
>
> Which one is faster?
>
> select * from Table A where period > '20060420' and period < '20060517'
>
> OR
>
> select * from Table B where period > to_date(20060420) and period <
> to_date(20060517)
>

So for the character it is 8 bytes and for the date is is 7 bytes. SO the date index is smaller and thus could be more effecient.(also you should specify the date format and not rely on implicit conversion, eg to_date('20060420,'yyyymmdd') ) You should NOT store dates in character fields! You store characters in character fields, numbers in number fields, dates in date fields. If you store dates in character fields how many days are between the strings '20060420' and '20060519'? The optimizer can't use the information if you store dates as strings. (poor design) Jim Received on Tue May 16 2006 - 22:36:47 CDT

Original text of this message

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