indexed date

Hi Friends,

I am always confused about selecting columns with date datatypes and the performance associated to it:rolleyes:
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 :confused: because i am
reformatting the column with a function right?

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


Thanks hunnie

use a function based index

Or query for the appropriate range ...

Code:

---

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?)

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

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

Thanks

same just that I dont use concats

Thank you all :)

Quote:

---

Originally Posted by slimdave

(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? :)

Quote:

---

Originally Posted by yxez

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.

Quote:

---

Originally Posted by abhaysk

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".

yeah dear...its just an example, actually im thinking of the encoding date :D