I needed to gather stats for only a single column. For whatever reason, the auto stats job was not getting it, but we actually found our execution plans were much better when we had it.
I ran what is below to generate a HEIGHT BALANCED histogram with 254 evenly divided buckets. We have five years of data, so assuming the data is evenly distributed (ours roughly is), I would expect buckets such as…
BUCKET LOW HIGH
1 JUL-21-2004 JUL-28-2004
2 JUL-29-2004 AUG-4-2004
3 AUG-5-2004 AUG-12-2004
...
254 JUL-14-2009 JUL-21-2009
This will allow the optimizer to take the value and decide whether the number of rows found on average in that bucket is low enough to produce a good execution plan by using its index.
begin
dbms_stats.gather_table_stats('RSPROD_ARCH',
'REQUEST',
ESTIMATE_PERCENT => 0.1,
METHOD_OPT => 'FOR COLUMNS CLOSEDDATE SIZE 254');
end;
/
2 comments for “How to gather stats for a single column”