Does this look like a bug? Test case enclosed ...

Old statistics are left in DBA_TAB_COL_STATISTICS when doing dbms_stats gather_table_stats with different METHOD_OPT than the previous run. Can bypass by doing a delete_table_stats before the ( different ) gather_table_stats.

Similar results observed with going the gather_schema_stats instead of individual tables.

Here's my test case ... please change in the SOMEOWNER to something that works for you.

drop table SOMEOWNER.testmeout;

create table SOMEOWNER.testmeout(col1 number, col2 varchar2(20), col3 date ) logging noparallel;

create unique index SOMEOWNER.testmeout_index on SOMEOWNER.testmeout (col1) logging noparallel;

insert into SOMEOWNER.testmeout(col1, col2, col3) values(1,'Row 1',sysdate);
insert into SOMEOWNER.testmeout(col1, col2, col3) values(2,'Row 2',sysdate);
insert into SOMEOWNER.testmeout(col1, col2, col3) values(3,'Row 3',sysdate);
commit;

• following statement may not be available in all versions ... uncomment if it works in your version ...
• select dbms_stats.get_param('METHOD_OPT') from dual;

exec dbms_stats.gather_table_stats
(OWNNAME=>'SOMEOWNER',TABNAME=>'TESTMEOUT',CASCADE=>TRUE);

• default for METHOD_OPT ( should be 'FOR ALL COLUMNS SIZE AUTO' );

select * from dba_tab_col_statistics where table_name='TESTMEOUT';

• Will return 3 rows ...

exec dbms_stats.gather_table_stats
(OWNNAME=>'SOMEOWNER',TABNAME=>'TESTMEOUT',METHOD_OPT=>'FOR ALL INDEXED COLUMNS',cascade=>TRUE);

select * from dba_tab_col_statistics where table_name='TESTMEOUT';

• Still returning 3 rows ... 2 of them are left over from last table statistics ...

execute dbms_stats.delete_table_stats
(OWNNAME=>'SOMEOWNER',TABNAME=>'TESTMEOUT'); exec dbms_stats.gather_table_stats
(OWNNAME=>'SOMEOWNER',TABNAME=>'TESTMEOUT',METHOD_OPT=>'FOR ALL INDEXED COLUMNS',cascade=>TRUE);

select * from dba_tab_col_statistics where table_name='TESTMEOUT';

• Now only 1 row is returned ...