GATHER_TABLE_STATS vs GATHER_SCHEMA_STATS

To all,

Have tar on this open, but support has been slow with updates.

9.2.0.7 EE Sun 5.9 64 Bit.

Anyway have this table, query is:

SELECT record_key, t_id, '31-DEC-2099', 0 FROM target_tab
WHERE current_row = 'X'

There is a bitmap index on current_row and it has two distinct values. For some reason (only in our prod) environment, DBMS_STATS.GATHER_SCHEMA_STATS (method_opt=> all indexed columns size 254) is generating the wrong data on that column. A quick check of dba_histograms shows an endpoint number of 0 and 1 after gather_Schema_stats. It does (properly according to 10053 trace and its stats) a full table scan.

However, gather_table_Stats generates proper date (endpoints being 24035 and 5952162) and after it is run the query uses the index.

I'm leaning towards this being a bug, but was wondering if anyone else had seen this behavior from DBMS_STATS before?

Thanks Received on Thu Feb 28 2008 - 08:14:53 CST