I have a fairly large (10s of millions rows) Oracle 10g table with a date field in it, which in a number of queries is constrained to a range like this:
... AND date_field >= TO_DATE(:lowerDate, 'YYYY-MM-DD') AND date_field < TO_DATE(:upperDate, 'YYYY-MM-DD') ...
This constraint is usually restricting the table content to between thousands and hundreds of thousands records. There are also indexes to support those queries as well which also include
date_field and are used by Oracle according to
Then I realised that user input dates in that constraint never include time, so the variance of
date_field can be cut down greatly by using
So I tried changing that constraint to this (without changing the index which still only lists the field and not the function):
... AND TRUNC(date_field) >= TO_DATE(:lowerDate, 'YYYY-MM-DD') AND TRUNC(date_field) < TO_DATE(:upperDate, 'YYYY-MM-DD') ...
When written like this, the query performs quicker and its cost in
EXPLAIN is 3 times lower (varies between the exact queries, but a typical example is down from 12000 to 4000). The index is still shown as employed.
But when the index is also adjusted from including
TRUNC(date_field), and this is paired with a constraint rewritten like above, the cost of the query goes down to about 200 (60 times lower!)
This is of course good news for me, but I don't understand why is that happening, since a range constraint should in my understanding make no difference. Regardless of whether its a date + time of just the date, the effort needed to tell whether a value is question is earlier or later than that point in time remains the same.
Clearly there is something I don't see yet, so what can that be? Could it be, for example, just a side effect of the index now being smaller and so more efficiently navigated or anything like this?