Histograms

[aji.thm]

Hai,
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 ..?


Thanks ...Thomas

[TerryD]

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.

Terry

[aji.thm]

Terry,

But when i analyze the table itself it is creating a histogram. Why should i Specifically create a new one...? And what is a bucket size..?


...thomas

[TerryD]

Yes, there are entries in there, but without the for clause it creates a histogram with a bucket size of 1... The optimiser doesn't consider this as a histogram.

If you create a histogram, you will see more entries for the column.

e.g. (on my db)

analyze table t_entity compute statistics;
select * from dba_histograms where table_name = 'T_ENTITY' and column_name = 'COUNTRY_ID'
--> 2 rows selected

analyze table t_entity compute statistics for columns COUNTRY_ID size 75;
(75 is default)
select * from dba_histograms where table_name = 'T_ENTITY' and column_name = 'COUNTRY_ID'
--> 14 rows selected

Terry