Can any one say what is the need of creating a histogram . I had noticed that when i analyze the table itself it is putting the entry in dba_histogram table. Is this sufficient ..? what is the need of specifying bucket size..? Does this boost up any performance ..?
Creating a histogram gives the optimiser more information on indexed columns where data is skewed.
For example (From my system):
I have a Country column which contains 12 different values. The table has 3.5 million rows.
A bitmap index is created on the table, but one day the optimiser changed to a full table scan over an index scan.
Since some countries are quite new in this table, the have only a few records e.g. maybe a couple of thousand. I created a histogram on this column, and the optimiser went back to using the index for most countries.
So it is not guarenteed to enhance performance, but may lead to better performance since the optimiser can make better choices on it's execution plan.