#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    14
    Rep Power
    0

    Why a certain date makes query faster


    Hello to all.

    I have a query with a few tables in join, and I filter data with something like

    where mytable.initial_date > sysdate-30

    If I use any date, it takes about 5 seconds to run, but if I use

    to_date('01010001','ddmmrrrr')

    instead of any other dates, it takes just a few milliseconds.
    Now, it's just a curiosity, but why that date makes the query VERY fast? Does Oracle treat it in some special way? Maybe it knows every date is greatest than that date and doesn't consider the filter?

    Thanks for the answers
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,057
    Rep Power
    383
    You should examine the Explain Plan for each version,
    that should give you the answer.

    If you want to improve performance, review questions like.
    Is the column indexed?
    Are statistics up to date?
    What is the cardinality of the column?

    Comments on this post

    • medialint agrees
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    14
    Rep Power
    0
    The execution plan for both versions is the same, that's why I am confused. The column is not indexed, statistics are up to date and cardinality is the same for both versions of the query.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,057
    Rep Power
    383
    The execution plan for both versions is the same
    That does seem odd.
    I don't mean to sound patronizing; but are you sure a new Explain Plan was generated?

    If the column is not indexed then I don't think statistics or cardinality will come into play;
    but someone may know better (someone always does ).

    Have you tried using the current date - 30 instead and seeing what you get?
    e.g.
    SQL Code:
    WHERE mytable.initial_date > to_date('04292012','ddmmrrrr')

    Comments on this post

    • gregorio.palama disagrees : that's exactly what made me open the thread...
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    14
    Rep Power
    0
    that's exactly why I opened the thread: I've used two different dates, one is to_date('01010001','ddmmrrrr'), the second one is any other different date.

    I know it is odd the execution plan is exactly the same, and that's why it made me curious to know what's behind this different times of execution..
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,057
    Rep Power
    383
    gregorio.palama disagrees: that's exactly what made me open the thread...
    Actually, it is not!

    This is how you opened the thread
    Code:
    where mytable.initial_date > sysdate-30
    While this may seem trivial, when you are trying to diagnose some strange behavior,
    it is very important to eliminate all variations, however slight.

    Clive
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    14
    Rep Power
    0
    Originally Posted by clivew
    Actually, it is not!

    This is how you opened the thread
    Code:
    where mytable.initial_date > sysdate-30
    While this may seem trivial, when you are trying to diagnose some strange behavior,
    it is very important to eliminate all variations, however slight.

    Clive
    I'm sorry Clive, but my initial post clearly says

    If I use any date, it takes about 5 seconds to run
    and "any" includes sysdate-30, but to_date('10102012','mmddrrrr') and to_date('12122011','mmddrrrr') too!

    Anyways, I don't really want to discuss what my initial post was, since I think the strange behavior seems to be very clear to me..

    The only thing that is not clear is the reason of that behavior!
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,057
    Rep Power
    383
    Lets just agree to differ

    Hope you find your solution.

IMN logo majestic logo threadwatch logo seochat tools logo