RE: Histograms - SIZE clause & num_buckets anomaly (with apiggybackquestion)
- From: "Charu Joshi" <joshic@xxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 7 Aug 2004 18:16:10 +0530
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:
1. 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).
2. 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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Wolfgang Breitling
Sent: Tuesday, August 03, 2004 9:47 PM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
- » RE: Histograms - SIZE clause & num_buckets anomaly (with apiggybackquestion)
