Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have a relatively classic scenario where the date clause of the query generated by the application uses a "greater than" for a given date.

Because there is no end clause to the date range, oracle doesn't choose to use the date index and ends up doing a very expensive table scan instead. However we know this data is temporal, so we know there is no data in the future. So if we modify the sql to include an end date of sysdate for the time period then the query uses the index and it becomes very cheap.

Unfortunately, due to a bug in the application we use we are unable to change the application. So is there any way in Oracle that I can either "teach" oracle that the max data in that table is today, so it can make a clever decision, or is the only real way to solve this problem at the database level to pin the plan?

Interesting - the statistics are indeed up to date. So why wouldnt it be using them? Could it be something that has since been improved in oracle 11?
–
CodekJan 9 '12 at 14:44

Certainly the CBO advances with each release. It'll never be perfect but it is always better to find out why it chooses an 'incorrect' path before resorting to hints etc - we'd need to know more about your exact situation to dig deeper: eg are you using bind variables (if so bind peeking might be in play). Are you using histograms? Can you reproduce the problem with a clone of the table (perhaps with a restricted set of data)?
–
Jack Douglas♦Jan 9 '12 at 15:20

We are using bind variables yes. I'll read up on that! And find out about the histograms.
–
CodekJan 9 '12 at 15:38

yes we are using histograms, but weirdly for this column it only has 2 entries. Seems odd for a date field ( the data isnt skewed either ) anyway i'll investigate further, thanks
–
CodekJan 9 '12 at 15:46

2

@Codek great - do drop by the site again - you sound like someone who might be able to answer some of the questions here as well as asking, and we need that for Oracle :-)
–
Jack Douglas♦Jan 9 '12 at 16:17