analyze worked better than gather stats

Oracle 10.2.0.3 on HP-UX 11i.

 

I have a large, busy table.  It is 70,000,000+ rows and 1.3GB in size.

 

The gather stats job runs automatically as needed.

 

There are 11 indexes on this table.  One the indexes has as its first
column, a date column (named event_dt).

 

When doing a simple query such as:

 

Select * from TABLE where event_dt > '27-OCT-2008' performs a full table
scan.  The explain_plan estimates that 800,000+ rows is to be returned.

 

I then manually ran a gather stats as follows:

 

exec dbms_stats.gather_table_stats ('XXX','XXXXXX', method_opt => 'FOR
ALL COLUMNS SIZE 1', degree => dbms_stats.auto_degree, CASCADE => TRUE);

 

It did not help.  Out of desperation, I ran an old fashioned "analyze
table XXX estimate statistics;"  

 

This ran very quickly and corrected the issue, as the explain_plan is
now correct (estimated rows is 7,000) and it runs with the index.

 

So, I cannot figure out why the old analyze worked, where the new
gather_stats did not.

 

Can anyone offer any advice on this? 

 

Thanks all,

 

 

 

John Fedock

"K" Line America, ISD Department

Other related posts: