等频直方图预估结果集行计算公式

公式：摘自《催华-基于ORACLE的SQL优化》

链接地址：http://blog.csdn.net/launch_225/article/details/25472129

等频：唯一值《=桶数

=>等频(频率)直方图 
-->density计算
density=1/(2*num_rows*null_adjust)
null_adjust=(num_rows-num_nulls)/num_rows
-->等值查询,val在low_value and high_value之间,值等于某个endpoint_value
cardinality=num_rows * selectivity
selectivity=bucketsize/num_rows
bucketsize=current_endpoint_number-previous_endpoint_number

 TABLE_NAME	COLUMN_NAME	NUM_BUCKETS	LOW_VALUE	HIGH_VALUE	HISTOGRAM
1	T1	N1	13	C102	C113	FREQUENCY



-->density计算
density=1/(2*num_rows*null_adjust)

SQL> select 1/(2*18*1) from dual;

1/(2*18*1)
----------
.027777778

   	OWNER	TABLE_NAME	NUM_DISTINCT	DENSITY	COLUMN_NAME
1	AIKI	T1	13	0.0277777777777778	N1


-->等值查询,val在low_value and high_value之间,值等于某个endpoint_value
cardinality=num_rows * selectivity
selectivity=bucketsize/num_rows
bucketsize=current_endpoint_number-previous_endpoint_number

  1*  select * from t1 where n1=6
SQL> /


Execution Plan
----------------------------------------------------------
Plan hash value: 1577308413

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     3 |    15 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1        |     3 |    15 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_T1_N1 |     3 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("N1"=6)

          
OWNER	TABLE_NAME	COLUMN_NAME	ENDPOINT_NUMBER	ENDPOINT_VALUE
AIKI	T1	N1	1	1
AIKI	T1	N1	3	2
AIKI	T1	N1	4	4
AIKI	T1	N1	5	5       --previous_endpoint_number
AIKI	T1	N1	8	6       --current_endpoint_number
AIKI	T1	N1	9	9
AIKI	T1	N1	10	10
AIKI	T1	N1	11	11
AIKI	T1	N1	13	12
AIKI	T1	N1	14	14
AIKI	T1	N1	15	15
AIKI	T1	N1	17	16
AIKI	T1	N1	18	18

bucketsize=8-5=3;
selectivity=3/18
cardinality=18*(3/18)=3;