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

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 TRUNC().

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 date_field to 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?

  • 1
    less values to select from compared to normal date values where you could have 86400 (potential) values for each calendar date. Less to scan, less work which means quicker results. BTW if you have this issue, partitioning on dates with local indexes would be another option if your db version supports that and you have the license. – Raj Jul 17 '17 at 18:39
  • Thanks, a 'side effect' from the smaller size is my only explanation as well. Partitioning is not an option in this particular case, but thanks for a hint. – Yuriy Jul 17 '17 at 18:44
  • 1
    Is the index compressed? Does the cardinality estimate from the table remain the same as the cost goes down? – Joe Obbish Jul 18 '17 at 1:27
  • Yes, cardinality also goes down with this change (which is much easier to see why...) – Yuriy Jul 18 '17 at 9:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.