Oracle not using index when > used in where clause

Question

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?

Rgds, Dan

(Oracle 10 Solaris Sparc)

Answer 1

Oracle should be able to use the statistics to determine the high and low values for any column if you are keeping them up to date:

drop table foo;
create table foo(id date primary key);
insert into foo select sysdate-level from dual connect by level<31;
select low_value, high_value from dba_tab_columns where table_name='FOO';
/*
LOW_VALUE HIGH_VALUE
-------------------------------------------- ------------------------------------------ 
*/
execute dbms_stats.gather_table_stats('JDOUGLAS', 'FOO');
select low_value, high_value from dba_tab_columns where table_name='FOO';
/*
LOW_VALUE HIGH_VALUE
-------------------------------------------- ------------------------------------------
786F0C0A0E0837 787001080E0837
*/

• Codek: 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?
• Jack Douglas: 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)?
• Codek: We are using bind variables yes. I'll read up on that! And find out about the histograms.
• Codek: 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
• Jack Douglas: @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 :-)