Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Histograms - SIZE clause & num_buckets anomaly

RE: Histograms - SIZE clause & num_buckets anomaly

From: Lex de Haan <lex.de.haan_at_naturaljoin.nl>
Date: Tue, 3 Aug 2004 13:44:34 +0200
Message-ID: <JFEEIGBIDOCCDALDIPLNMELJCGAA.lex.de.haan@naturaljoin.nl>


Hi Charudatta,
I think you said it all yourself already :-)

choosing anything "blindly" sounds dangerous/adventurous, isn't it? first of all, your data distribution should be the main factor, not the number of distinct values; if you have an even data distribution, histograms will give you no benefits at all.

if your data is skewed, and if you can afford the maintenance overhead, having f.b. histograms provides the ultimate precision -- but I certainly think you should test this.

about your example (a column with 100 distinct values) it could be that e.g. 10 h.b. buckets will be enough to guide the optimizer properly.

in my histogram testing, I typically look at two things: - how does the estimated CBO costs change for a typical SQL statement - how many popular values show up

you probably know this, but popular values are values showing up more than once as an endpoint value in an h.b. histogram; they are treated in a special way by the CBO for equality searches.

Kind regards,
Lex.



visit my website at http://www.naturaljoin.nl

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Charudatta Joshi Sent: Tuesday, August 03, 2004 12:28
To: oracle-l_at_freelists.org
Subject: RE: Histograms - SIZE clause & num_buckets anomaly

Hi Lex,

Been thinking about this comment of yours:

>>I agree that frequency histograms give the ultimate information about a
>>column population, compared with height-based histograms, but on the other
>>hand the performance should be "comparable" -- that is, the height-based
>>histogram with a reasonably high number of buckets shouldn't give bad
>>execution plans.

Currently, wherever possible & applicable, I (blindly) have the number of buckets equal to the number of distinct values. This means that whenever number of distinct values is less than 255, that is my bucket size.

Given the fact that histogram maintenance can be very expensive, I now wonder if I should try to get the same benefit using less number of buckets. I guess in this case the logic for determining the bucket size will vary from case to case. But in general:

I realize the answers can vary from case to case, still a general opinion will be much appreciated.

Thanks & regards,
Charu.



Disclaimer:

This message (including any attachments) contains confidential information intended for a specific individual and purpose, and is protected by law. If you are not the intended recipient, you should delete this message and are hereby notified that any disclosure, copying, or distribution of this message, or the taking of any action based on it, is strictly prohibited.



Visit us at http://www.mahindrabt.com

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------


-- Binary/unsupported file stripped by Ecartis --
-- Type: text/x-vcard
-- File: Lex de Haan.vcf


----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Received on Tue Aug 03 2004 - 06:40:24 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US