本文共 4357 字,大约阅读时间需要 14 分钟。
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 dictionaryviews 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 ofrows 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 valueindicated 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 a20col LOW_VALUE for a20col HIGH_VALUE for a20set 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-07select 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 3FLEAF_CLASSID 1 72FLEAF_CLASSID 2 147FLEAF_CLASSID 3 2052FLEAF_CLASSID 4 2128FLEAF_CLASSID 5 3071FLEAF_CLASSID 6 4043FLEAF_CLASSID 7 5085FLEAF_CLASSID 8 6003FLEAF_CLASSID 9 6077FLEAF_CLASSID 10 7019FLEAF_CLASSID 11 7089FLEAF_CLASSID 12 7160FLEAF_CLASSID 13 7233FLEAF_CLASSID 14 8066FLEAF_CLASSID 15 9019FLEAF_CLASSID 16 9089FLEAF_CLASSID 17 9164FLEAF_CLASSID 18 10061FLEAF_CLASSID 19 11073FLEAF_CLASSID 20 12041FLEAF_CLASSID 21 20013FLEAF_CLASSID 22 20083FLEAF_CLASSID 23 20152FLEAF_CLASSID 24 20529FLEAF_CLASSID 25 20603FLEAF_CLASSID 26 21017FLEAF_CLASSID 27 21529FLEAF_CLASSID 28 21599FLEAF_CLASSID 29 22039FLEAF_CLASSID 30 22115FLEAF_CLASSID 31 22558FLEAF_CLASSID 32 22631FLEAF_CLASSID 33 23053FLEAF_CLASSID 34 23126FLEAF_CLASSID 35 23566FLEAF_CLASSID 36 24003FLEAF_CLASSID 37 24075FLEAF_CLASSID 38 24149FLEAF_CLASSID 39 24549FLEAF_CLASSID 40 24624FLEAF_CLASSID 41 24707FLEAF_CLASSID 42 24816FLEAF_CLASSID 43 24936FLEAF_CLASSID 44 25038FLEAF_CLASSID 45 25130FLEAF_CLASSID 46 25196FLEAF_CLASSID 47 25261FLEAF_CLASSID 48 25347FLEAF_CLASSID 49 25415FLEAF_CLASSID 50 25480FLEAF_CLASSID 51 25545FLEAF_CLASSID 52 25611FLEAF_CLASSID 53 25677FLEAF_CLASSID 54 25743FLEAF_CLASSID 55 25809FLEAF_CLASSID 56 25875FLEAF_CLASSID 57 25943FLEAF_CLASSID 58 26012FLEAF_CLASSID 59 26079FLEAF_CLASSID 60 26144FLEAF_CLASSID 61 26209FLEAF_CLASSID 62 26328FLEAF_CLASSID 63 26393FLEAF_CLASSID 64 26458FLEAF_CLASSID 65 26527FLEAF_CLASSID 66 26594FLEAF_CLASSID 67 26666FLEAF_CLASSID 68 26739FLEAF_CLASSID 69 26819FLEAF_CLASSID 70 26892FLEAF_CLASSID 71 26998FLEAF_CLASSID 72 27071FLEAF_CLASSID 73 27141FLEAF_CLASSID 74 27260FLEAF_CLASSID 75 2732676 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相关分析文章是
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/94384/viewspace-600298/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/94384/viewspace-600298/