性能陷阱：Oracle表连接中范围比较
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了性能陷阱：Oracle表连接中范围比较，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含3179字，纯文字阅读大概需要5分钟。
内容图文
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 <= 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.
内容总结
以上是互联网集市为您收集整理的性能陷阱：Oracle表连接中范围比较全部内容，希望文章能够帮你解决性能陷阱：Oracle表连接中范围比较所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。