-
indexed date
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
Behind The Success And Failure Of A Man Is A Woman
-
use a function based index
-
Thank you all
Behind The Success And Failure Of A Man Is A Woman
-
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?)
-
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?
Behind The Success And Failure Of A Man Is A Woman
-
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.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
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
Behind The Success And Failure Of A Man Is A Woman
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|