Why there are both filter and access predicates on the same index on this execution plan?

Question

Considering the execution plan for this query :

SQL_ID  1m5r644say02b, child number 0
-------------------------------------
select * from hr.employees where department_id = 80 intersect select * 
from hr.employees where first_name like 'A%'
 
Plan hash value: 1738366820
 
------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |             |      1 |        |      4 |00:00:00.01 |       8 |       |       |          |
|   1 |  INTERSECTION                         |             |      1 |        |      4 |00:00:00.01 |       8 |       |       |          |
|   2 |   SORT UNIQUE                         |             |      1 |     34 |     34 |00:00:00.01 |       6 |  6144 |  6144 | 6144  (0)|
|*  3 |    TABLE ACCESS FULL                  | EMPLOYEES   |      1 |     34 |     34 |00:00:00.01 |       6 |       |       |          |
|   4 |   SORT UNIQUE                         |             |      1 |     11 |     10 |00:00:00.01 |       2 |  2048 |  2048 | 2048  (0)|
|   5 |    TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES   |      1 |     11 |     10 |00:00:00.01 |       2 |       |       |          |
|*  6 |     INDEX SKIP SCAN                   | EMP_NAME_IX |      1 |     11 |     10 |00:00:00.01 |       1 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - filter("DEPARTMENT_ID"=80)
   6 - access("FIRST_NAME" LIKE 'A%')
       filter("FIRST_NAME" LIKE 'A%')

The execution plan has both access and filter predicates with the same A% predicate here on the EMP_NAME_IX index. But shouldn't the access predicate be enough here, as they both will filter the same rows? Why did it perform the additional filter predicate?

Is there a general rule for when both access and filter are the same? Based on GV$SQL_PLAN, when an operation has either an access or a filter predicate, they are only equal about 1% of the time. And this situation only happens with operations and options like INDEX (FULL/RANGE/SKIP/UNIQUE) and SORT (JOIN/UNIQUE).

select *
from gv$sql_plan
where access_predicates = filter_predicates;

Answer 1

Presumably you have an index on hr.employees that includes the first_name column. But you are selecting * from hr.employees such that the rows obtained from the index would have to traced back (i.e. join) with the table.

For conceptual understanding it helps to think of indexes as plain tables with a foreign key to the original table's primary key. When usage of indexes helps, these two tables are joined. The index is used alone when it contains all needed columns.

In this case we assume a join is required since you are selecting *. When accessing the hr.employee table for the second query of the intersect, because its where clause filters on an index column, a join to the index is performed prior to filtering.

The first occurrence of "FIRST_NAME" LIKE 'A%' is the reason usage of the index is decided. The second occurrence, is then the actual filtering. Filtering happens only once, not twice.

These are listed as distinct operations as deciding to use the index (and therefore perform the join) has its own costs.