-
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
Thomas
-
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
-
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
Thomas
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|