[20190630]如何确定直方图类型.txt

[20190630]如何确定直方图类型.txt http://dbaparadise.com/2018/06/which-histogram-will-oracle-pick/ let's explore how will Oracle decide which type of histograms to use. Oracle is using 4 variables to determine the type of histogram to use. These variables are listed below: 1) number of distinct values in the column -> NDV. If the column in the table has the values red, blue, yellow, green,    then NDV=4 2) number of histogram buckets -> n. The default value for n is 254. If you don't explicitly specify the number of    buckets, then by default Oracle will set the number of buckets to 254. 3) internal variable, percentage threshold, p = (1–(1/n)) * 100; This is a formula set by Oracle. 4) the value of estimate_percent, when you gather statistics, whether or not it's set to default value AUTO_SAMPLE_SIZE,    when gathering statistics. --//number of distinct values in the column -> NDV --//n 指建立直方图桶的数量。缺省时254? Here are the rules, set by Oracle when picking a specific type of histogram: Oracle will pick a Frequency Histogram if: NDV < n (n=254 by default) if number of distinct values is less than the number of histogram buckets. --//如果NDV<254 ,选择频率直方图. Oracle will pick a Height Balanced Histogram if: NDV > n (n=254 by default) estimate_percent < > AUTO_SAMPLE_SIZE if number of distinct values is greater than the number of histogram buckets and the estimate percent is not set to AUTO_SAMPLE_SIZE during stats gathering. --//如果NDV>254,estimate_percent < > AUTO_SAMPLE_SIZE,选择Height Balanced Histogram. Oracle will pick a Hybrid Histogram if: NDV > n (n=254 by default) estimate_percent = AUTO_SAMPLE_SIZE percentage of rows for top n (n=254 by default) frequent values < p If n=254, then p is 99.6 --// (1-1/254) *100  = 99.60629921259842519700 --//如果NDV > n,estimate_percent = AUTO_SAMPLE_SIZE. --//流行值的比例 < p,选择Hybrid Histogram. Oracle will pick a Top Frequency Histogram if NDV > n (n=254 by default) estimate_percent = AUTO_SAMPLE_SIZE percentage of rows for top n (n=254 by default) frequent values >= p --//如果NDV > n,estimate_percent = AUTO_SAMPLE_SIZE. --//流行值的比例 >= p,选择Top Frequency Histogram. --//注：要生成Top Frequency Histogram 或者 Hybrid Histogram都要分析时estimate_percent = AUTO_SAMPLE_SIZE。 --//P的计算公式如下: --//(1-1/n)*100= (1-1/254)*100 = 99.60629921259842519700