Lately I met a case that the range filter predicates due to wrong cardinality issue Lets check the following query
最近遇到一个由于范围过滤导致错误基数而引起的性能问题让我们来看下面的查询
The real records number is around
真实的记录数大约千百万
The explain plan shows optimizer think it has good filtration So put this JOIN in the first order Actually it is totally wrong
执行计划显示这里优化器认为它有良好的过滤芯所以把它放在一个多个表JOIN的第一位置显然它完全错了
SQL> set autotrace traceonly explain;
SQL> set linesize
SQL> SELECT
TDURATIONSECSQTY TIMEINSECONDS
TMONEYAMT MONEYAMOUNT
TWAGEAMT WAGEAMOUNT
TAPPLYDTM APPLYDATE
TADJAPPLYDTM ADJUSTEDAPPLYDATE
TSTARTDTM
TENDDTM
THOMEACCOUNTSW
FROM
TKCSOWNERWFCTOTAL T
TKCSOWNERPAYCODEMMFLAT MP
WHERE
MPEFFECTIVEDTM <= TAPPLYDTM
AND MPEXPIRATIONDTM > TAPPLYDTM
AND MPPAYCODEID = TPAYCODEID
/
| Id | Operation | Name | Rows | Bytes | Cost |
| | SELECT STATEMENT | | | K| |
|* | HASH JOIN | | | K| |
| | TABLE ACCESS FULL| PAYCODEMMFLAT | | | |
| | TABLE ACCESS FULL| WFCTOTAL | K| M| |
Now let me comment the range filter
让我注释到范围条件看
MPEFFECTIVEDTM <= TAPPLYDTM
AND MPEXPIRATIONDTM > TAPPLYDTM
SQL> SELECT
TDURATIONSECSQTY TIMEINSECONDS
TMONEYAMT MONEYAMOUNT
TWAGEAMT WAGEAMOUNT
TAPPLYDTM APPLYDATE
TADJAPPLYDTM ADJUSTEDAPPLYDATE
TSTARTDTM
TENDDTM
THOMEACCOUNTSW
FROM
TKCSOWNERWFCTOTAL T
TKCSOWNERPAYCODEMMFLAT MP
WHERE
/* MPEFFECTIVEDTM <= TAPPLYDTM
AND MPEXPIRATIONDTM > TAPPLYDTM*/
MPPAYCODEID = TPAYCODEID
/
Execution Plan
Plan hash value:
| Id | Operation | Name | Rows | Bytes | Cost |
| | SELECT STATEMENT | | M| M| |
|* | HASH JOIN | | M| M| |
| | INDEX FAST FULL SCAN| PK_PAYCODEMMFLAT | | | |
| | TABLE ACCESS FULL | WFCTOTAL | K| M| |
The Cardinality show it is already close to the correct value
基础是已经接近正确结果了
So how optimizer work out the cardinality with range filter in TABLE JOIN ?
那么优化器怎么出来表连接中的范围扫描呢？
The answer is % always %
答案是%
* % * % = This is exact equal to the result of test
So if you meet any performance issue with range filter in TBALE JOIN I am not surprise I think Oracle need to improve the CBO to get better support on such situation
