Histograms - SIZE clause & num_buckets anamoly
- From: "Charudatta Joshi" <joshic@xxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 2 Aug 2004 17:02:41 +0530
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@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:
- » Histograms - SIZE clause & num_buckets anamoly
