The only easy way to analyze your problem might be just to compare the 2 statistics(one by gather, one by analyze) Can you post the table statistics of both cases? Dion Cho 2008/11/1 Fedock, John (KAM.RIC) <John.Fedock@xxxxxxxxxxxx> > 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 > > >