indexed date
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: indexed date

  1. #1
    Join Date
    May 2005
    Location
    Boracay
    Posts
    681

    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

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    use a function based index

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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?)
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    where hiredate >= to_date('2006-01-01','YYYY-MM-DD')
    and hiredate < to_date('2006-01-02','YYYY-MM-DD')

  5. #5
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    Agree with Dave :-)
    Pando, what's the difference between your query and the one proposed by Dave?

    Thanks

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    same just that I dont use concats

  7. #7
    Join Date
    May 2005
    Location
    Boracay
    Posts
    681
    Thank you all
    Behind The Success And Failure Of A Man Is A Woman

  8. #8
    Join Date
    May 2005
    Location
    Boracay
    Posts
    681
    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?
    Behind The Success And Failure Of A Man Is A Woman

  9. #9
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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.
    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"

  10. #10
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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".
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width