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

来源：互联网发布：域名主机查询编辑：程序博客网时间：2024/06/10 00:25
摘自《催华-基于ORACLE的SQL优化》
链接地址：http://blog.csdn.net/launch_225/article/details/25474427
SQL> desc t1 Name                                      Null?    Type ----------------------------------------- -------- ---------------------------- N1                                                 NUMBER V1                                                 VARCHAR2(8)SQL> select * from t1;        N1 V1---------- --------         1 a         2 b         2 c         4 c         5 d         6 e         6 f         6 f         9 g        10 h        11 i        12 i        12 i        14 j        15 k        16 l        16 m        18 n18 rows selected. TABLE_NAMECOLUMN_NAMENUM_BUCKETSLOW_VALUEHIGH_VALUEHISTOGRAM1T1N110C102C113HEIGHT BALANCED OWNERTABLE_NAMECOLUMN_NAMEENDPOINT_NUMBERENDPOINT_VALUE1AIKIT1N1012AIKIT1N1123AIKIT1N1244AIKIT1N1465AIKIT1N15106AIKIT1N16127AIKIT1N17148AIKIT1N18159AIKIT1N191610AIKIT1N110181.查询值为popular value:cardinality=num_rows * SELECTIVITYSELECTIVITY=(BUCKETS_THIS_POPULAR_VALUE/BUCKETS_TOTAL)* NULL_ADJUSTNULL_ADJUST=(NUM_ROWS-NUM_NULLS)/NUM_ROWSBUCKETS_THIS_POPULAR_VALUE:POPULAR VALUE所点的bucket的数量，buckets_total:bucket的总数select * from t1 where n1=6;SELECTIVITY=(2/10)*1SQL> select (2/10)*1*18 from dual;(2/10)*1*18-----------        3.6        SQL> select * from t1 where n1=6;Execution Plan----------------------------------------------------------Plan hash value: 1577308413-----------------------------------------------------------------------------------------| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |-----------------------------------------------------------------------------------------|   0 | SELECT STATEMENT            |           |     4 |    20 |     2   (0)| 00:00:01 ||   1 |  TABLE ACCESS BY INDEX ROWID| T1        |     4 |    20 |     2   (0)| 00:00:01 ||*  2 |   INDEX RANGE SCAN          | IDX_T1_N1 |     4 |       |     1   (0)| 00:00:01 |-----------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("N1"=6)   --2.非popular value--(>=10.2.0.4版本)selectivity=newdensity * null_adjustnull_adjust=(num_rows-num_nulls)/num_rowsnewdensity=(buckets_total-buckets_all_popular_values)/buckets_total/(ndv-popular_value.count)olddensity=sum(np.count(i) * np.count(i))/((num_rows-num_nulls)* sum(np.count(i)))ndv=num_distinctolddensity存储在dba_***_col_statistics的density中buckets_all_popular_values:所有的pupular value所占的bucket的数量，buckets_total:bucket的总数popular_values.count:pupular value的个数，NP.count(i):每个nopopular value在目标表中的记录数           newdensity=(10-(4-2))/10/(13-1)selectivity=(10-(4-2))/10/(13-1)cardinality=(10-(4-2))/10/(13-1)*18SQL> select  (10-(4-2))/10/(13-1)*18 from dual;(10-(4-2))/10/(13-1)*18-----------------------                    1.2                    SQL> select * from t1 where n1=12;  --12为非popular valueExecution Plan----------------------------------------------------------Plan hash value: 1577308413-----------------------------------------------------------------------------------------| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |-----------------------------------------------------------------------------------------|   0 | SELECT STATEMENT            |           |     1 |     5 |     2   (0)| 00:00:01 ||   1 |  TABLE ACCESS BY INDEX ROWID| T1        |     1 |     5 |     2   (0)| 00:00:01 ||*  2 |   INDEX RANGE SCAN          | IDX_T1_N1 |     1 |       |     1   (0)| 00:00:01 |-----------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("N1"=12)                --2.1.版本10.2.0.4(不含10.2.0.4,10.2.0.1)以上版本selectivity=olddensity * null_adjustnull_adjust=(num_rows-num_nulls)/num_rowsolddensity=sum(np.count(i)* np.count(i))/((num_rows-num_nulls)*sum(np.count(i)))olddensity存储在dba_***_col_statistics的density中NP.count(i):每个nopopular value在目标表中的记录数--2.1版本10.2.0.1selectivity=olddensity * null_adjustnull_adjust=(num_rows-num_nulls)/num_rowsolddensity=sum(dv.count(i) * dv.count(i))/((num_rows-num_nulls)* sum(dv.count(i)))olddensity用于实际计算可选择率和结果集的Cardinalityolddensity也会存储在数据字典DBA_TAB_COL_STATISTICS,DBA_PART_COL_STATISTICS和DBA_SUBPART_COL_STATISTICS中的DENSITY中DV.count(i)表示的是目标列的每个DISTINCT值在目标表中的记录数
0 0