Histogram bucket limitations
Histograms are limited to 254 buckets so if there are more than 254 distinct values and there is no single value that dominates the column's dataset then histograms may not provide helpful statistics. With large numbers of distinct values, histogram usage can be further impacted because differences in non popular values cannot be recorded. The choice of 254 buckets per column histogram was a balance between accuracy of the statistics and the speed of histogram collection/amount of space required to store the information.
As of 12c, the maximum possible number of buckets in a histogram increases from 254 to 2,048.
Limitations of Histogram on Character Columns
Histograms only store the first 32 characters of a character string (5 characters pre 8.1.6 See Bug:598799 ). If histograms are gathered on character columns that have data that is longer than 31 characters and the first 31 characters are identical then column histograms may not reflect the cardinality of these columns correctly as these values will all be treated as if they are identical. There are also similar limits with numeric data which is normalised to 15 digits in histogram endpoints.
As of 12c, the maximum possible number of characters considered for a histogram on a string column increases from thirty-two to sixty-four.
ANALYZE command gathers incorrect num_distinct in every release if the length of character column is more than 32 characters. (64 characters in 12c)
DBMS_STATS gathers incorrect num_distinct for such columns in releases prior to 11g.
Histograms are incorrect for such columns in any case.
In this case, run DBMS_STATS with the following attribute to get correct num_distinct. This option will delete the histogram on the column. The absence of histogram is generally better for this column.
METHOD_OPT=> 'FOR COLUMNS <column_name> SIZE 1'
From 11g, DBMS_STATS with METHOD_OPT=> 'FOR ALL COLUMNS SIZE N' (N >1) also generates correct num_distinct. But, it still generate incorrect histogram.
列值超过32个字符时，直方图无效。
