[20190630]如何確定直方圖型別.txt

[20190630]如何確定直方圖型別.txt



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