Lately, I met a case that the range filter predicates due to wrong cardinality issue. Let’s check the following query.
最近遇到一个由于范围过滤导致错误基数而引起的性能问题。让我们来看下面的查询：
The real records number is around 38,000,000.
真实的记录数大约3千8百万
The explain plan shows 72838, optimizer think it has good filtration. So put this JOIN in the first order. Actually , it is totally wrong.
执行计划显示72838，这里优化器认为它有良好的过滤芯，所以把它放在一个多个表JOIN的第一位置。显然，它完全错了。
SQL> set autotrace traceonly explain;
SQL> set linesize 999
SQL> SELECT
2 T.DURATIONSECSQTY TIMEINSECONDS,
T.MONEYAMT MONEYAMOUNT,
T.WAGEAMT WAGEAMOUNT,
T.APPLYDTM APPLYDATE,
T.ADJAPPLYDTM ADJUSTEDAPPLYDATE,
T.STARTDTM,
T.ENDDTM,
T.HOMEACCOUNTSW
FROM
TKCSOWNER.WFCTOTAL T,
TKCSOWNER.PAYCODE1MMFLAT MP
WHERE
MP.EFFECTIVEDTM <= T.APPLYDTM
AND MP.EXPIRATIONDTM > T.APPLYDTM
AND MP.PAYCODEID = T.PAYCODEID
/
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 72838 | 5192K| 37450 |
|* 1 | HASH JOIN | | 72838 | 5192K| 37450 |
| 2 | TABLE ACCESS FULL| PAYCODE1MMFLAT | 323 | 6783 | 3 |
| 3 | TABLE ACCESS FULL| WFCTOTAL | 8938K| 443M| 37317 |
Now, let me comment the range filter.
让我注释到范围条件看：
“MP.EFFECTIVEDTM <= T.APPLYDTM
AND MP.EXPIRATIONDTM > T.APPLYDTM”
SQL> SELECT
2 T.DURATIONSECSQTY TIMEINSECONDS,
T.MONEYAMT MONEYAMOUNT,
T.WAGEAMT WAGEAMOUNT,
T.APPLYDTM APPLYDATE,
T.ADJAPPLYDTM ADJUSTEDAPPLYDATE,
T.STARTDTM,
T.ENDDTM,
T.HOMEACCOUNTSW
FROM
TKCSOWNER.WFCTOTAL T,
TKCSOWNER.PAYCODE1MMFLAT MP
WHERE
/* MP.EFFECTIVEDTM <= T.APPLYDTM
AND MP.EXPIRATIONDTM > T.APPLYDTM*/
MP.PAYCODEID = T.PAYCODEID 3 4 5 6 7 8 9 10 11 12 13 14 15 16
17 /
Execution Plan
----------------------------------------------------------
Plan hash value: 564403449
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 29M| 1583M| 37405 |
|* 1 | HASH JOIN | | 29M| 1583M| 37405 |
| 2 | INDEX FAST FULL SCAN| PK_PAYCODE1MMFLAT | 323 | 1615 | 1 |
| 3 | TABLE ACCESS FULL | WFCTOTAL | 8938K| 443M| 37317 |
The Cardinality show 29,135,142 , it is already close to the correct value.
基础是29,135,142，已经接近正确结果了。
So how optimizer work out the cardinality with range filter in TABLE JOIN
那么优化器怎么出来表连接中的范围扫描呢？
The answer is 5%, always 5%.
答案是5%
29135142 * 5% * 5% = 72837.8 , This is exact equal to the result of test 1.