Same filter and access predicates in Oracle explain plan

Question

I have a users table with a username column, and a B-tree index in this column. When I check the explain plan of the following query: select username from users where username like 'A%' I can see that Oracle performs an index range scan on the index to extract the required information, which is absolutely OK, but during the index range scan Oracle uses the following predicates: access(username like 'A%') filter(username like 'A%').

Why does Oracle use both of them? They are completely the same, so I think one of them would be enough.

Answer 1

This is not answer, just some set of links and quotations:

Access predicate (“access”) The access predicates express the start and stop conditions of the leaf node traversal.

Index filter predicate (“filter” for index operations) Index filter predicates are applied during the leaf node traversal only. They do not contribute to the start and stop conditions and do not narrow the scanned range.

Table level filter predicate (“filter” for table operations) Predicates on columns that are not part of the index are evaluated on table level. For that to happen, the database must load the row from the table first.

In brief, as Tom Kyte said:

Access means we are using something to "access" the data - we only "access" relevant data. Filter means we are getting more data then we need, and we filter it after we get it. The filter will be applied to all rows and only those that pass the filter

https://asktom.oracle.com/pls/apex/f?p=100:11:::NO:RP:P11_QUESTION_ID:7807480400346035212

There also good video on Oracle Youtube channel: https://www.youtube.com/watch?v=K0f2Yzue7yw

Answer 2

The philosophy of Oracle (IMO) is opposite of what you expect. The FILTER predicate is not only reduced in obvious cases, but sometimes expanded with additional filter conditions.

Small example

 select  * from tst where c1 like 'A%'  and c1 like '%A'

yield

    2 - access("C1" LIKE 'A%')
   filter("C1" IS NOT NULL AND "C1" LIKE 'A%' AND "C1" LIKE '%A')

So additional NOT NULL constraint is added.

I can only speculate, that the filter processing is cheaper that access (in memory), so double checking is better than few CPU cycles. Good possible is also that Oracle must handle special cases where the access may deliver more than defined in the predicate, such as access on cluster tables.