Access and Filter predicates in Oracle execution plan

Question

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

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

The corresponding operation is INDEX RANGE scan on index, which is defined on fields (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?

Answer 1

No, the access predicates in this example indicates that the index is being traversed by both DOMAIN_CODE and 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).

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

Answer 2

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.

However, if in this case adding CURRENT_VERSION_IND as the second column of the index improves performance significantly on frequently run queries and doesn't harm the performance of other queries, then it may make sense to do so.