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.<\/p>\n
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…<\/p>\n
\r\nBUCKET LOW HIGH\r\n1 JUL-21-2004 JUL-28-2004\r\n2 JUL-29-2004 AUG-4-2004\r\n3 AUG-5-2004 AUG-12-2004\r\n...\r\n254 JUL-14-2009 JUL-21-2009\r\n<\/pre>\nThis 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.<\/p>\n
\r\nbegin\r\n dbms_stats.gather_table_stats('RSPROD_ARCH', \r\n 'REQUEST',\r\n ESTIMATE_PERCENT => 0.1, \r\n METHOD_OPT => 'FOR COLUMNS CLOSEDDATE SIZE 254');\r\nend;\r\n\/\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"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…<\/p>\n