indexed date

[yxez]

Hi Friends,

I am always confused about selecting columns with date datatypes and the performance associated to it
Example:

sql> select hiredate from EMP where hiredate='01-JAN-06';

no rows selected.

sql> select hiredate from EMP where trunc(hiredate)='01-JAN-06';

hiredate
--------
01-JAN-06

If I have indexed on hiredate and the EMP contains million rows...
then it will be doing full table scan because i am
reformatting the column with a function right?

How can I write the query to use the index performance?


Thanks hunnie

[davey23uk]

use a function based index

[slimdave]

Or query for the appropriate range ...

select hiredate
from EMP
where hiredate between to_date('2006-01-01','YYYY-MM-DD') and 
to_date('2006-01-01'||' 23:59:59','YYYY-MM-DD HH24:MI:SS')

(in the real world of course hire date is unlikely to have a time component, but I guess this is just an example?)

[pando]

where hiredate >= to_date('2006-01-01','YYYY-MM-DD')
and hiredate < to_date('2006-01-02','YYYY-MM-DD')

[Bore]

Agree with Dave :-)
Pando, what's the difference between your query and the one proposed by Dave?

Thanks

[pando]

same just that I dont use concats

[yxez]

Thank you all

[yxez]

(in the real world of course hire date is unlikely to have a time component, but I guess this is just an example?)

Doesnt it contain time if u default it to sysdate dear?

[abhaysk]

Doesnt it contain time if u default it to sysdate dear?

That would be a dumb design/coding.. you shud have used trunc(sysdate) while laoding.. makes lots of sense.

[slimdave]

That would be a dumb design/coding.. you shud have used trunc(sysdate) while laoding.. makes lots of sense.

+1

In general people wouldn't have a hire date that included a component like "10:15:27" ... it would just be a date. In fact I think that it's unlikely that sysdate would be a good default for a hire date -- almost certainly that would be populated "in advance".