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

摘自《催华-基于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 n

18 rows selected.


 TABLE_NAME	COLUMN_NAME	NUM_BUCKETS	LOW_VALUE	HIGH_VALUE	HISTOGRAM
1	T1	N1	10	C102	C113	HEIGHT BALANCED

 OWNER	TABLE_NAME	COLUMN_NAME	ENDPOINT_NUMBER	ENDPOINT_VALUE
1	AIKI	T1	N1	0	1
2	AIKI	T1	N1	1	2
3	AIKI	T1	N1	2	4
4	AIKI	T1	N1	4	6
5	AIKI	T1	N1	5	10
6	AIKI	T1	N1	6	12
7	AIKI	T1	N1	7	14
8	AIKI	T1	N1	8	15
9	AIKI	T1	N1	9	16
10	AIKI	T1	N1	10	18



1.查询值为popular value:
cardinality=num_rows * SELECTIVITY
SELECTIVITY=(BUCKETS_THIS_POPULAR_VALUE/BUCKETS_TOTAL)* NULL_ADJUST
NULL_ADJUST=(NUM_ROWS-NUM_NULLS)/NUM_ROWS
BUCKETS_THIS_POPULAR_VALUE:POPULAR VALUE所点的bucket的数量，buckets_total:bucket的总数

select * from t1 where n1=6;

SELECTIVITY=(2/10)*1
SQL> 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_adjust
null_adjust=(num_rows-num_nulls)/num_rows
newdensity=(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_distinct
olddensity存储在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)*18

SQL> 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 value
Execution 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_adjust
null_adjust=(num_rows-num_nulls)/num_rows
olddensity=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.1
selectivity=olddensity * null_adjust
null_adjust=(num_rows-num_nulls)/num_rows
olddensity=sum(dv.count(i) * dv.count(i))/((num_rows-num_nulls)* sum(dv.count(i)))

olddensity用于实际计算可选择率和结果集的Cardinality
olddensity也会存储在数据字典DBA_TAB_COL_STATISTICS,DBA_PART_COL_STATISTICS和DBA_SUBPART_COL_STATISTICS中的DENSITY中
DV.count(i)表示的是目标列的每个DISTINCT值在目标表中的记录数