Oracle执行计划中的访问和筛选谓词 [英] Access and Filter predicates in Oracle execution plan

问题描述

Oracle执行计划中的Access和Filter谓词有什么区别? 如果我理解正确，则使用访问"来确定需要读取哪些数据块，并在读取数据块后应用过滤器".因此，过滤是邪恶的".

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".

在以下执行计划的谓词信息部分的示例中:

In the example of Predicate Information section of the execution plan below:

10 - access("DOMAIN_CODE"='BLCOLLSTS' AND "CURRENT_VERSION_IND"='Y')
     filter("CURRENT_VERSION_IND"='Y')

为什么在访问"和过滤器"部分中都重复"CURRENT_VERSION_IND"?

why "CURRENT_VERSION_IND" is repeated in both Access and Filter sections?

相应的操作是对索引进行INDEX RANGE扫描，该扫描在字段(DOMAIN_CODE，CODE_VALUE，CURRENT_VERSION_IND，DECODE_DISPLAY)上定义.

The corresponding operation is INDEX RANGE scan on index, which is defined on fields (DOMAIN_CODE, CODE_VALUE, CURRENT_VERSION_IND, DECODE_DISPLAY).

我的猜测是，因为CURRENT_VERSION_IND不是索引的第二列，所以Oracle在Access阶段不能使用它.因此，它按DOMAIN_CODE列访问索引，获取所有块，然后按CURRENT_VERSION_IND对其进行过滤.我说的对吗?

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?

推荐答案

否，本例中的访问谓词表明索引同时被DOMAIN_CODE和CURRENT_VERSION_IND遍历.

No, the access predicates in this example indicates that the index is being traversed by both DOMAIN_CODE and CURRENT_VERSION_IND.

我不会担心筛选谓词看起来是多余的-这似乎是一个解释计划的怪癖，这可能与它必须对索引进行某种跳过扫描有关(它在第一列上进行范围扫描，然后在CODE_VALUE上跳过扫描，搜索任何匹配的CURRENT_VERSION_IND s.)

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).

您是否需要修改索引或创建另一个索引完全是另一回事.

Whether you need to modify the index or create another index is another matter entirely.

此外，为了纠正一个小小的误解:在执行访问"或过滤"步骤之前，必须先从索引中获取块，然后才能执行任何操作.如果您指的是从表中获取块，那么答案也是否定的-您说过滤谓词"10"是在索引访问上，而不是在表访问上.而且无论如何，Oracle没有理由无法对索引的CURRENT_VERSION_IND进行过滤器评估-它根本不需要访问表，除非它需要索引中未包含的其他列.

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_IND on the index - it doesn't need to access the table at all, unless it needs other columns not included in the index.

这篇关于Oracle执行计划中的访问和筛选谓词的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！