This doesn't answer the question, but did you inspect the values of the various statistics columns in dba_tables and dba_indexes after both the dbms_stats and analyze runs? That information might provide some clues. Paul Baumgartel CREDIT SUISSE Information Technology Prime Services Databases Americas One Madison Avenue New York, NY 10010 USA Phone 212.538.1143 paul.baumgartel@xxxxxxxxxxxxxxxxx www.credit-suisse.com ________________________________ From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Fedock, John (KAM.RIC) Sent: Friday, October 31, 2008 4:09 PM To: oracle-l@xxxxxxxxxxxxx Subject: 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 ============================================================================== Please access the attached hyperlink for an important electronic communications disclaimer: http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html ==============================================================================