What is the difference between "Access predicates" 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 is "CURRENT_VERSION_IND" 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?
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.