Oracle 执行计划中的访问和过滤谓词
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1464469/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Access and Filter predicates in Oracle execution plan
提问by Sergey Stadnik
What is the difference between Access and Filter predicates in Oracle execution plan? If I understand correctly, "access" is used to determine which data blocks need to be read, and "filter" is applied after the blocks are read. Hence, filtering is "evil".
Oracle 执行计划中的 Access 和 Filter 谓词有什么区别？如果我理解正确，“访问”用于确定需要读取哪些数据块，并在读取块后应用“过滤器”。因此，过滤是“邪恶的”。
In the example of Predicate Information section of the execution plan below:
在下面执行计划的 Predicate Information 部分的示例中：
10 - access("DOMAIN_CODE"='BLCOLLSTS' AND "CURRENT_VERSION_IND"='Y')
filter("CURRENT_VERSION_IND"='Y')
why "CURRENT_VERSION_IND" is repeated in both Access and Filter sections?
为什么“CURRENT_VERSION_IND”在“访问”和“过滤器”部分中重复出现？
The corresponding operation is INDEX RANGE scan on index, which is defined on fields (DOMAIN_CODE, CODE_VALUE, CURRENT_VERSION_IND, DECODE_DISPLAY).
对应的操作是INDEX RANGE scan on index，定义在字段（DOMAIN_CODE、CODE_VALUE、CURRENT_VERSION_IND、DECODE_DISPLAY）上。
My guess is that because CURRENT_VERSION_IND is not the second column in the index, Oracle can't use it during the Access stage. Hence, it accesses index by DOMAIN_CODE column, fetches all the blocks, and then filters them by CURRENT_VERSION_IND. Am I right?
我的猜测是因为 CURRENT_VERSION_IND 不是索引中的第二列，所以 Oracle 在 Access 阶段无法使用它。因此，它通过 DOMAIN_CODE 列访问索引，获取所有块，然后通过 CURRENT_VERSION_IND 过滤它们。我对吗？
采纳答案by Jeffrey Kemp
No, the access predicates in this example indicates that the index is being traversed by both DOMAIN_CODEand CURRENT_VERSION_IND.
不，在这个例子中访问的谓词表示该指数是由两个穿越DOMAIN_CODE和CURRENT_VERSION_IND。
I wouldn't worry about the filter predicate that appears to be redundant - it seems to be a quirk of explain plan, probably something to do with the fact that it has to do a sort of skip-scan on the index (it does a range scan on the first column, then a skip scan over CODE_VALUE, searching for any matching CURRENT_VERSION_INDs).
我不会担心看起来多余的过滤器谓词 - 它似乎是解释计划的一个怪癖，可能与它必须对索引执行某种跳过扫描的事实有关（它执行范围扫描第一列，然后跳过扫描CODE_VALUE，搜索任何匹配的CURRENT_VERSION_INDs)。
Whether you need to modify the index or create another index is another matter entirely.
是否需要修改索引或创建另一个索引完全是另一回事。
Also, just to correct a minor misunderstanding: the blocks have to be fetched from the index BEFORE it can do anything, whether executing the "access" or "filter" steps. If you're referring to fetching blocks from the table, then also the answer is no - you said the filter predicate "10" was on the index access, not on a table access; and anyway, there's no reason Oracle can't evaluate the filter on CURRENT_VERSION_INDon the index - it doesn't need to access the table at all, unless it needs other columns not included in the index.
另外，为了纠正一个小误解：必须从索引中获取块，然后才能执行任何操作，无论是执行“访问”还是“过滤”步骤。如果您指的是从表中获取块，那么答案是否定的 - 您说过滤谓词“10”是针对索引访问，而不是针对表访问；无论如何，Oracle 没有理由不能CURRENT_VERSION_IND在索引上评估过滤器——它根本不需要访问表，除非它需要索引中未包含的其他列。
回答by Tony Andrews
I believe you are correct in your assessment of what Oracle is doing, but wrong to say that the filter step (or any other optimizer choice) is always "evil". It doesn't make sense to index absolutely every possible combination of columns that may be queried on, so filtering is frequently required.
我相信您对 Oracle 正在做什么的评估是正确的，但错误地说过滤步骤（或任何其他优化器选择）总是“邪恶的”。绝对索引可能被查询的列的所有可能组合是没有意义的，因此经常需要过滤。
However, if in this case adding CURRENT_VERSION_IND as the second column of the index improves performance significantly on frequently run queries anddoesn't harm the performance of other queries, then it may make sense to do so.
但是，如果在这种情况下将 CURRENT_VERSION_IND 添加为索引的第二列，可以显着提高频繁运行的查询的性能并且不会损害其他查询的性能，那么这样做可能是有意义的。
