Take the 2-minute tour ×
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?

Rgds, Dan

(Oracle 10 Solaris Sparc)

share|improve this question
add comment

1 Answer 1

up vote 3 down vote accepted

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
*/
share|improve this answer
    
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? –  Codek Jan 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. –  Codek Jan 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 –  Codek Jan 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
show 5 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.