性能陷阱：Oracle表连接中范围比较

Lately, I met a case that the range filter predicates due to wrong cardinality issue. Letrsquo;s check the followin

　　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

　　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

　　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

　　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.

　　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.