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