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

来源：互联网发布：淘宝掌柜名称怎么修改编辑：程序博客网时间：2024/05/19 06:47

公式：摘自《催华-基于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_valuecardinality=num_rows * selectivityselectivity=bucketsize/num_rowsbucketsize=current_endpoint_number-previous_endpoint_number TABLE_NAMECOLUMN_NAMENUM_BUCKETSLOW_VALUEHIGH_VALUEHISTOGRAM1T1N113C102C113FREQUENCY-->density计算density=1/(2*num_rows*null_adjust)SQL> select 1/(2*18*1) from dual;1/(2*18*1)----------.027777778   OWNERTABLE_NAMENUM_DISTINCTDENSITYCOLUMN_NAME1AIKIT1130.0277777777777778N1-->等值查询,val在low_value and high_value之间,值等于某个endpoint_valuecardinality=num_rows * selectivityselectivity=bucketsize/num_rowsbucketsize=current_endpoint_number-previous_endpoint_number  1*  select * from t1 where n1=6SQL> /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)          OWNERTABLE_NAMECOLUMN_NAMEENDPOINT_NUMBERENDPOINT_VALUEAIKIT1N111AIKIT1N132AIKIT1N144AIKIT1N155       --previous_endpoint_numberAIKIT1N186       --current_endpoint_numberAIKIT1N199AIKIT1N11010AIKIT1N11111AIKIT1N11312AIKIT1N11414AIKIT1N11515AIKIT1N11716AIKIT1N11818bucketsize=8-5=3;selectivity=3/18cardinality=18*(3/18)=3;

0 0