Histograms - SIZE clause & num

From: Charudatta Joshi <joshic_at_mahindrabt.com>
Date: Mon, 2 Aug 2004 17:02:41 +0530
Message-ID: <MHEAIPLKCACENJKNJIALIEOGCDAA.joshic@mahindrabt.com>

Hi all,

Version:- 9.2.0.1 Enterprise Edition
OS: Win2K

Have RTFM, STFW, RTFAT etc. etc. without much luck. Here's the case:

On gathering stats with different options of SIZE clause I am getting different (an unexpected) number of buckets. Please see below:

Column OWNER of table T4 contains skewed data. SQL> select owner, count(*) from t4 group by owner;

OWNER                            COUNT(*)
------------------------------ ----------
CAJ                                    15
CDJ                                     1
CJ                                     16
HR                                   1088
OLAPSYS                             14176
OUTLN                                 224
PERFSTAT                             2752
REPOS                               26496
SYS                                131392
SYSTEM                              12256
WMSYS                                4128

11 rows selected.

Deleting old stats, just in case.. SQL> begin 2 dbms_stats.delete_table_stats(ownname=>'CJ', 3 tabname=>'T4'); 4 end; 5 /

PL/SQL procedure successfully completed.

Collect stats with SIZE <n> clause in method_opt.
n=11 since 11 distinct values for OWNER. SQL> begin 2 dbms_stats.gather_table_stats(ownname=>'CJ', 3 tabname=>'T4', 4 method_opt=>'FOR COLUMNS SIZE 11 OWNER', 5 cascade=>TRUE); 6 end; 7 / PL/SQL procedure successfully completed.
Check the number of buckets. SQL> select num_distinct, num_buckets 2 from user_tab_col_statistics 3 where table_name = 'T4' and column_name = 'OWNER';

NUM_DISTINCT NUM_BUCKETS
------------ -----------

11 4

How come there are only four buckets created even though I specified size as 11? Could it be histogram compression?

Delete stats again: SQL> begin 2 dbms_stats.delete_table_stats(ownname=>'CJ', 3 tabname=>'T4'); 4 end; 5 /

PL/SQL procedure successfully completed.

Collect stats with SIZE SKEWONLY clause: SQL> begin 2 dbms_stats.gather_table_stats(ownname=>'CJ', 3 tabname=>'T4', 4 method_opt=>'FOR COLUMNS SIZE SKEWONLY OWNER', 5 cascade=>TRUE); 6 end; 7 /

PL/SQL procedure successfully completed.

Check the number of buckets. SQL> select num_distinct, num_buckets 2 from user_tab_col_statistics 3 where table_name = 'T4' and column_name = 'OWNER';

NUM_DISTINCT NUM_BUCKETS
------------ -----------

11 10

Now there are 10 buckets which is correct. But why this discrepancy? Can anybody please enlighten? Any references regarding Histograms (especially Histograms compression) will be highly appreciated. I haven't copied USER_TAB_HISTOGRAMS output here. Please let me know if any other information is required.

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

Received on Mon Aug 02 2004 - 06:28:17 CDT

Histograms - SIZE clause & num_buckets anamoly