ORACLE 多表关联基数计算

oracle版本11.2.0.4.0 - 64bit 

sql：

SELECT /*+parallel(0)*/ /*+dynamic_sampling(T 0)*/   COUNT(*)
  FROM REPORT.MID_SAP_REAL_DATA T,T_DIM_PRO_TCL_CN b
 WHERE t.artnr=b.product_id
  and b.category_name='彩电'
  and "T"."TRANSACTION_DATE" >=
       TO_DATE(' 2016-11-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
   and NVL(T.KNDNR,0) <> 'M1100816'

---------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Rows    | Bytes     | Cost  | Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |       1 |        53 | 52405 | 00:10:29 |
|   1 |   SORT AGGREGATE             |                   |       1 |        53 |       |          |
| * 2 |    HASH JOIN                 |                   | 2165588 | 114776164 | 52405 | 00:10:29 |
| * 3 |     INDEX FAST FULL SCAN     | INDEX_DPTC_PC     |    9113 |    191373 |    14 | 00:00:01 |
|   4 |     PARTITION RANGE ITERATOR |                   | 2412152 |  77188864 | 52370 | 00:10:29 |
| * 5 |      TABLE ACCESS FULL       | MID_SAP_REAL_DATA | 2412152 |  77188864 | 52370 | 00:10:29 |
---------------------------------------------------------------------------------------------------

   第五行基数计算：((19552-1)/19552*(17115029-12758)+12758)/17115029 * (35+4/5)/254 * 17115029=2412152.45547198 ≈2412152.46

详细见：http://blog.csdn.net/xg_87/article/details/53909425  ORACLE 双谓词基数计算

第3行基数计算：

OWNER	TABLE_NAME	COLUMN_NAME	ENDPOINT_NUMBER	ENDPOINT_VALUE	ENDPOINT_ACTUAL_VALUE
REPORT	T_DIM_PRO_TCL_CN	CATEGORY_NAME	53	1.19181E+36
REPORT	T_DIM_PRO_TCL_CN	CATEGORY_NAME	9166	1.19288E+36
REPORT	T_DIM_PRO_TCL_CN	CATEGORY_NAME	10143	1.19288E+36

使用频率直方图计算：9166-53=9113

表连接基数计算：

sel=(17115029-12758)/17115029/10143=9.85166689614434E-5

 outer (9113.000000) * inner (2412152.455472) * sel (0.000099)=2165588.052816