效能陷阱：Oracle表串連中範圍比較

　　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.

　　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.