The factors to consider are the number of buckets required to recognise popular values by getting two or more endpoints of the same value; whether the remaining non-popular values are uniformly distributed or skewed; and the number of distinct values. The reason for the first is fairly obvious. The reason for using the maximum number of buckets (254) if the non-popular values are skewed is for the selectivity of range predicates. The reason for the last is that if the number of distinct values is less than or equal to the number of buckets, Oracle does not use a height-balanced histogram, but uses one bucket for each value and stores the number of occurrences in place of the ENDPOINT_NUMBER. Please have a look at the APT script consider_histogram.sql on the Ixora web site for the details on how to put all this together.

Could you please tell how I could estimate the number of buckets for histograms to improve the selectivity?