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?
(Oracle 10 Solaris Sparc)