| Regarding Histograms [message #152262] |
Thu, 22 December 2005 00:43  |
chandanbhamra
Messages: 84
Registered: April 2005
Location: India
|
Member |
|
|
Hi,
I have some queries regarding histograms.
I want to know, how do i determine the no. of buckets while generating histograms. I have one table which contains 200 Million rows and i have to generate histogram for one of the columns. This columns have 55 Millions rows with value 0. Initially this column histogram contains only one bucket and when we try to execute the query with this column = 0 then it was taking lot of time. When i increase the no. of buckets to 10 then its executing fine. It was just a guess that i made for this column to set the no. of buckets.
1) I want to know what i can see to set the no. of buckets ?
2) How to drop histograms means i don't want to maintain any histogram for table or for any column ?
3) If there is any space that will be allocated to histogram. if yes, then where i can check the space but i think this histogram information is maintained in data dictonary tables.
4) When i generate the execution plan after generating the histogram its cost was very high as compared to execution plan taken before the histograms generation. Why is it so ?
I hope i am clear what i want to ask. Please let me know if somebody needs any other information in this.
Thanks in advance
Chandan Singh
|
|
|
|
| Re: Regarding Histograms [message #152328 is a reply to message #152262] |
Thu, 22 December 2005 09:33  |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
Histograms can be a complex subject and I won't pretend to know all about them yet. But so far I've found the best approach to just use as many buckets as you can, as many as the maximum you can specify (which is 250 or 254 or something like that). Oracle will only actually create as many buckets as you have unique values (up to the max) so even if you only have 10 distinct values and specify 250 buckets then you'll only get 10 buckets. But the idea in my mind is to provide the most information possible to the CBO, and that means more buckets which will lead to a higher degree of granularity in the bucket ranges. I think that is most of your questions covered, if not post a followup. Oh and yeah stored in the dictionary.
Also, I hope that 200 million row table is partitioned.
And, if those 55 million values that are 0 should really be null, and you are using 0 as a "fake" value to simulate null, then consider changing that so it really is a null. Fake values like that can fool the optimizer.
|
|
|
|
| Re: Regarding Histograms [message #152896 is a reply to message #152262] |
Tue, 27 December 2005 10:28 |
contactkeval
Messages: 23
Registered: October 2005
|
Junior Member |
|
|
Steve Adams has written a wonderful article on this, unfortunately the links is not working right now.
www.ixora.com.au/newsletter/2001_04.htm
check if this comes up, it has answer to all your queries and he has also written a script which will suggest you number of buckets should be created on the column.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
