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)

"chrischee18" <chrischee18@gmail.com> wrote in message
news:1147835931.121667.82690@i40g2000cwc.googlegro ups.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

Jim, thanks for your time and info.
I'll work on it.

Chris.