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

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?

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.

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.

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

Actually, it is not!

This is how you opened the thread


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



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!

Lets just agree to differ

Hope you find your solution.