RE: Histograms - SIZE clause & num_buckets anomaly (with apiggybackquestion)

From: Charu Joshi <joshic_at_mahindrabt.com>
Date: Sat, 7 Aug 2004 18:16:10 +0530
Message-ID: <MHEAIPLKCACENJKNJIALGEFACEAA.joshic@mahindrabt.com>

Hi Wolfgang and Lex,

Sorry for the late reply. Thanks to replies from both of you the things are now clear to me. I was mixing up the two phenomena as summarized below:

Due to a 9i optimization, Oracle creates HB histogram instead of FG one unless the SIZE parameter specified is ~50% more than NDV (number of distinct values).
It being HB, Oracle stores only one row per popular value thereby reducing value in the num_buckets column. The Endpoint number indicates the actual number of buckets.

My guess is that the greater the num_rows in the table and smaller the NDV on a column, the more likelihood of the HB histogram misleading CBO. This is where the above behaviour (1.) would hurt the most, and should be avoided by specifying SIZE= 1.5*NDV (thereby creating FB) in case one cares about histograms.

Thanks & regards,
Charu.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Wolfgang Breitling Sent: Tuesday, August 03, 2004 9:47 PM
To: oracle-l_at_freelists.org
Subject: RE: Histograms - SIZE clause & num_buckets anomaly (with apiggybackquestion)

You have a compressed HB histogram - one with popular values - if dba_tab_columns.num_buckets < max(dba_histograms.endpoint_number). There is a slim theoretical chance that that condition is true for a frequency histogram, however, in general

num_buckets = 1 ( = max(endpoint_number) ==> no histogram
num_buckets = num_distinct-1 ==> frequency histogram
num_buckets = max(endpoint_number) ==> HB histogram without popular values

( this includes case one, treating the entire value range as one big bucket) num_buckets < max(endpoint_number) ==> HB histogram with popular values

At 06:33 AM 8/3/2004, you wrote:

>How to identify if the actual number of buckets is different from the value
>shown in NUM_BUCKETS column of USER_TAB_COLUMNS?
>
>Thanks & regards,
>Charu.

Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.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
-----------------------------------------------------------------


*********************************************************
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
-----------------------------------------------------------------

Received on Sat Aug 07 2004 - 07:41:39 CDT