Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: IS IT FASTER TO SELECT RECORDS BY CHARACTER OR BY DATE?
"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