[疑问]关于执行计划中Filter的含义

显示全部楼层 · 2010-10-8 09:28:52

请参考以下的内容。
select Item_Item_Id, InitCap(Item_Description)
from Item
where Item_Classification = 1
and Item_Item_Id Between 1000000 And 2700000
and Item_Item_Id Not In (Select Invitem_Item_Id
from Inventory_Item
where Invitem_Location_Id = '405')

call count cpu elapsed disk query current rows
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 27 20.87 21.24 0 4408 0 399
Totals 29 20.87 21.24 0 4408 0 399
Misses in library cache during parse: 0
Optimizer hint: CHOOSE
Parsing user id: 106 (C12462)
Rows Execution Plan
0 SELECT STATEMENT OPTIMIZER HINT: CHOOSE
572 FILTER
598 TABLE ACCESS (BY ROWID) OF 'ITEM'
599 INDEX (RANGE SCAN) OF 'ITEM_PK' (UNIQUE)
278790 INDEX (RANGE SCAN) OF 'INVITEM_PK' (UNIQUE)
这里的Filter是什莫意思？？
对于条件Item_Classification = 1的反应吗？？
类似于 Item_Item_Id Not In (Select Invitem_Item_Id
from Inventory_Item
where Invitem_Location_Id = '405')这样的条件都会产生Filter标志吗？？

千问 · 2010-10-8 09:28:52

前一阵子，我问过这个问题。高手们也解答过。参考一些文章，说这是一种让子查询不展开，让它嵌套(nest)在里面。通过NO_UNNEST的hint也可以触发。
参考文章如下，http://space.itpub.net/15415488/viewspace-663969
一般情况下，类似于 Item_Item_Id Not In (Select Invitem_Item_Id
from Inventory_Item
where Invitem_Location_Id = '405')这样的条件都会产生Filter操作吗？
哪位高手能详细地讲讲这个Filter操作吗？

千问 · 2010-10-8 09:28:52

这里的filter类似于nest loop，而不是谓词过滤
谓词过滤出现在predicate Infomation中

千问 · 2010-10-8 09:28:52

学习了。
请问sundog315兄弟，您平时用什莫方式看执行计划？？

千问 · 2010-10-8 09:28:52

ViadeaZhu's article you referenced in msg #2 is a good one.
The filter operation (as opposed to filter predicate condition) is like nested loop, except unlike and better than NL, it tries to cache the data from the driving table, 256 or 1024 rows depending on whether you're in 10g. See Jonathan Lewis's CBO book. The filter operation can also have more than 2 child operations. See Christian Antognini's book. Having said all that, you can simply think of filter as the same as NL for practical purposes, until you need a detailed analysis of the plan.
Under what condition will you get a filter operation in the plan? It's of course cost-based. Consider three choices, hash join, NL, filter. Many people have to suppress subquery nesting to demo a filter operation, otherwise they would get a hash join. But subquery unnesting is not a condition that definitely leads to filter. If you don't have enough distinct values, the cost for NL may be lower than filter and NL will be chosen.
Yong Huang

千问 · 2010-10-8 09:28:52

原帖由 sundog315 于 2010-10-18 08:53 发表
这里的filter类似于nest loop，而不是谓词过滤
谓词过滤出现在predicate Infomation中
跟nest loop有什么区别?驱动表构建成hash表?

千问 · 2010-10-8 09:28:52

> 跟nest loop有什么区别?
I listed two differences in #5.
Yong Huang

千问 · 2010-10-8 09:28:52

学习了。谢谢大家的回答。
》Yong Huang sir，
关于执行计划或者SQL调优的知识，有没有什么好的书，能够比较系统和详细地讲讲这方面的知识？
当然是有的。Yong Huang ，能不能给推荐一本！！！？
[ 本帖最后由 ccsnmoracle 于 2010-10-20 16:17 编辑 ]

千问 · 2010-10-8 09:28:52

> 关于执行计划或者SQL调优的知识，有没有什么好的书
Currently, the most highly recommended book in the world is Jonathan Lewis's "Cost-Based Optimizer". Another book almost equally praised is "Troubleshooting Oracle Performance" by Christian Antognini. The title sounds like it covers more than SQL tuning. But in my opinion, it's only about SQL tuning. The latter book is easier to read.
If you need one that is even easier than those two, any of Tom Kyte's books is the best choice. His writing style is as clear as a master can achieve.
The Performance Tuning book of Oracle documentation is also of high quality. It's shorter so it takes less time to go through, and you can just focus on SQL tuning by skipping instance and system tuning.
Yong Huang

千问 · 2010-10-8 09:28:52

Yong Huang Sir
十分感谢。