table column histogram .亲自实验检验得出的学习笔记。

-- 看来，学习还是要时常牢记那句话：

纸上得来终觉浅，绝知此事要躬行。

http://yong321.freeshell.org/oranotes/Histogram.html

http://www.oracle.com.cn/viewthread.php?tid=67935

表字段柱状图的理解：
-- dictionary :
dba_tab_col_statistics/user_tab_col_statistics
dba_tab_histogram/user_tab_histogram

-- definition:
the definition name is base the meaning of column endpoint_number.
1， 当字段列DISTINCT VALUE>255 时，只能建立HBH(HEIGHT-BALANCED)HISTOGRAM. 即：平衡树。
here endpoint_number is stand for card-weighted(记录行数加权值),
and endpoint_value is stand for the statistics-top-limit value(统计截止列值)。
calculate the full table scan result cardinality between valus of a specific column col1 is :
return card = ((endpoint-number1- endpoint-number2 ) PER (endpoint_value1 - endpoint_value2) )/ (MAX(endpoint-number)in the column bucket)
return card is (0,1), the closer to 1 and the larger of card .
成本值是个百分比，大于零，小于1，越接近1，越接近返回所有行记录。

2, 当字段列DISTINCT VALUE<=255 时，建立VBH(VALUE-BASED) HISTOGRAM 或者 FH(FREQUENCY-HISTOGRAM). 即：频率分部图。

--
endpoint-number:
1,HBH : cumulative card weight , used for (endpoint-number1 - endpoint-number2)/(MAX(endpoint-number)in the column bucket) get the return card percent of full table . 简记为：成本加权
2,FH : the frequency of the actual column value that is the same row of endpoint_value. 简记为：值为endpoint_value在表中的记录行数
endpoint-value:
1,HBH : the specific column value , used for (endpoint-value1 - endpoint-value2) 简记为：表的当前列值,只有区间使用才有意义(colvalue >endpoint_value1 and colvalue 2,FH : the actual column value that is stored in the database table. 简记为：表的当前列值，单独引用（=）或区间使用(>

---UNDERSTANDING:
in use HBH:
card weight = (endpoint-number1- endpoint-number2 ) PER (endpoint_value1 - endpoint_value2)
if the value is even distribute, then the difference of neighbor endpoint-number is around 1, and start with 0.
if the value is skew distribute, then the difference of neighbor endpoint-number is very large than others , maybe not start with 0.

in use FH:
endpoint-number IS standing for the cumulative value of rows AT every endpoint_value, so here the endpoint_value is actually equal to the database column value.
and we can conclude :
for a individual value search , such as where col1='val1' . then the return cardinality/rows is:
value (endpoint_number1- endpoint_number2) in user_tab_histogram(where table_name='tab1' and column_name='COL1' and endpoint_value=NUM_VAL1.
for a RANGE value search , such as where col1 > num1 and col1 value ( max(endpoint_number)- min(endpoint_number)) in user_tab_histogram(where table_name='tab1' and column_name='COL1'
and endpoint_value>NUM_VAL1 and endpoint_value

-- sample 例子：
exec dbms_stats.gather_table_stats(user,'TB1',estimate_percent => NULL, method_opt => 'FOR COLUMNS id SIZE AUTO' )
if you use the specified column , then the other unspecified column will not be statisticed.
estimate_percent => NULL means using compute statiscs ,that's 100 percent or full table statistics.
by default , parameter 'SIZE AUTO' will not generate histogram when the column value is distribute even. But you can enforce oracle generate
bucket by using parameter 'SIZE 10' (1..254).

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/350519/viewspace-1040168/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/350519/viewspace-1040168/