This post has been transferred to blog.tuningsql.com
See its new home here.
There are lots of resources that will tell you about the “access” and “filter” lines you see in the Predicates section of your execution plan. Most resources will tell you…”access is more efficient than filter.” And, generally, that’s true. Access tells us where to go in our index (thereby eliminating rows before we reach them) whereas filter tells us once we’ve arrived at a row that “it’s no good and must be discarded.”
But you may have noticed times when there seems to be and echo in your plan, and it repeats the same predicate filter in both the FILTER and ACCESS section of your execution plan. What then? Why does Oracle need both? Shouldn’t just one be enough?
The question came in today in an Orapub Slack conversation and I nearly exploded from excitement. Below was my response.