analyze的size 指定histogram的最大buckets,the default value is 75,minimum value is 1,and maximum value is 254.

------------quote begin----<>P1017-----------------

Column statistics appear in the data dictionary views USER_TAB_COLUMNS, ALL_
TAB_COLUMNS, and DBA_TAB_COLUMNS. Histograms appear in the data dictionary
views USER_TAB_HISTOGRAMS, DBA_TAB_HISTOGRAMS, and ALL_TAB_
HISTOGRAMS; USER_PART_HISTOGRAMS, DBA_PART_HISTOGRAMS, and ALL_
PART_HISTOGRAMS; and USER_SUBPART_HISTOGRAMS, DBA_SUBPART_
HISTOGRAMS, and ALL_SUBPART_HISTOGRAMS.
...........
Oracle Database does not create a histogram with more buckets than the number of
rows in the sample. Also, if the sample contains any values that are very repetitious,
then Oracle Database creates the specified number of buckets, but the value
indicated by the NUM_BUCKETS column of the ALL_, DBA_, and USER_TAB_
COLUMNS views may be smaller because of an internal compression algorithm.

----------------quote end--------------------------------------------

在實際環境中查得資料如下:

#獲取列上的統計資訊
col COLUMN_NAME for a20
col LOW_VALUE for a20
col HIGH_VALUE for a20
set linesize 1000
select COLUMN_NAME,NUM_DISTINCT,LOW_VALUE,HIGH_VALUE,DENSITY,NUM_NULLS,NUM_BUCKETS,LAST_ANALYZED
   from user_tab_columns
where table_name='C2CCLASSRELATION'
and LAST_ANALYZED is not null;
COLUMN_NAME          NUM_DISTINCT LOW_VALUE            HIGH_VALUE              DENSITY NUM_NULLS NUM_BUCKETS LAST_ANALYZE
-------------------- ------------ -------------------- -------------------- ---------- ---------- ----------- ------------
FLEAF_CLASSID                4913 C104                 C3034A1B             .000203542          0          75 04-FEB-07

select SAMPLE_SIZE,AVG_COL_LEN,CHAR_LENGTH,HISTOGRAM
   from user_tab_columns
where table_name='C2CCLASSRELATION'
and LAST_ANALYZED is not null;
SAMPLE_SIZE AVG_COL_LEN CHAR_LENGTH HISTOGRAM
----------- ----------- ----------- ---------------
       4913           5           0 HEIGHT BALANCED

##查列上histogram詳細資訊
col COLUMN_NAME for a15
col ENDPOINT_NUMBER for 99999999
col ENDPOINT_VALUE for 99999999
col ENDPOINT_ACTUAL_VALUE for a30
set head off
set pagesize 1000 linesize 1000
select COLUMN_NAME,ENDPOINT_NUMBER,ENDPOINT_VALUE,ENDPOINT_ACTUAL_VALUE from dba_tab_histograms where table_name='C2CCLASSRELATION';

FLEAF_CLASSID                 0              3
FLEAF_CLASSID                 1             72
FLEAF_CLASSID                 2            147
FLEAF_CLASSID                 3           2052
FLEAF_CLASSID                 4           2128
FLEAF_CLASSID                 5           3071
FLEAF_CLASSID                 6           4043
FLEAF_CLASSID                 7           5085
FLEAF_CLASSID                 8           6003
FLEAF_CLASSID                 9           6077
FLEAF_CLASSID                10           7019
FLEAF_CLASSID                11           7089
FLEAF_CLASSID                12           7160
FLEAF_CLASSID                13           7233
FLEAF_CLASSID                14           8066
FLEAF_CLASSID                15           9019
FLEAF_CLASSID                16           9089
FLEAF_CLASSID                17           9164
FLEAF_CLASSID                18          10061
FLEAF_CLASSID                19          11073
FLEAF_CLASSID                20          12041
FLEAF_CLASSID                21          20013
FLEAF_CLASSID                22          20083
FLEAF_CLASSID                23          20152
FLEAF_CLASSID                24          20529
FLEAF_CLASSID                25          20603
FLEAF_CLASSID                26          21017
FLEAF_CLASSID                27          21529
FLEAF_CLASSID                28          21599
FLEAF_CLASSID                29          22039
FLEAF_CLASSID                30          22115
FLEAF_CLASSID                31          22558
FLEAF_CLASSID                32          22631
FLEAF_CLASSID                33          23053
FLEAF_CLASSID                34          23126
FLEAF_CLASSID                35          23566
FLEAF_CLASSID                36          24003
FLEAF_CLASSID                37          24075
FLEAF_CLASSID                38          24149
FLEAF_CLASSID                39          24549
FLEAF_CLASSID                40          24624
FLEAF_CLASSID                41          24707
FLEAF_CLASSID                42          24816
FLEAF_CLASSID                43          24936
FLEAF_CLASSID                44          25038
FLEAF_CLASSID                45          25130
FLEAF_CLASSID                46          25196
FLEAF_CLASSID                47          25261
FLEAF_CLASSID                48          25347
FLEAF_CLASSID                49          25415
FLEAF_CLASSID                50          25480
FLEAF_CLASSID                51          25545
FLEAF_CLASSID                52          25611
FLEAF_CLASSID                53          25677
FLEAF_CLASSID                54          25743
FLEAF_CLASSID                55          25809
FLEAF_CLASSID                56          25875
FLEAF_CLASSID                57          25943
FLEAF_CLASSID                58          26012
FLEAF_CLASSID                59          26079
FLEAF_CLASSID                60          26144
FLEAF_CLASSID                61          26209
FLEAF_CLASSID                62          26328
FLEAF_CLASSID                63          26393
FLEAF_CLASSID                64          26458
FLEAF_CLASSID                65          26527
FLEAF_CLASSID                66          26594
FLEAF_CLASSID                67          26666
FLEAF_CLASSID                68          26739
FLEAF_CLASSID                69          26819
FLEAF_CLASSID                70          26892
FLEAF_CLASSID                71          26998
FLEAF_CLASSID                72          27071
FLEAF_CLASSID                73          27141
FLEAF_CLASSID                74          27260
FLEAF_CLASSID                75          27326

76 rows selected.

##the following is the command to collect the statistics of 'C2CCLASSRELATION'
EXECUTE DBMS_STATS.gather_table_stats(ownname=>'C2CDB',tabname=>'C2CCLASSRELATION',estimate_percent=>10,method_opt=>'FOR ALL INDEXED COLUMNS',cascade=>true,degree=>8);

所以,dbms_stats的size預設值與analyze一致,另外,如果不指定sample,預設是1(即100%)

yong huang對histogram相關分析文章是

about histogram(1)

相關文章