analyze worked better than gather stats
Date: Fri, 31 Oct 2008 16:08:34 -0400
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?
"K" Line America, ISD DepartmentReceived on Fri Oct 31 2008 - 15:08:34 CDT