Why Access and Filter Predicates are the same here?

Question

When I get the autotrace output of the query above using the Oracle SQL Developer, I see that the join condition is used for access and filter predicates. My question is, does it read all the department_ids from the DEPT_ID_PK and then use these IDs to access and filter the employees table? If so, why the employees table has full table scan? Why does it read the employees table again by using the department_ids of the departments table? Could anyone please read this execution plan step by step simply, and explain the reason why the access and filter predicates are used here?

Best Regards

Answer 1

it is a merge join (a bit like hash join, Merge join is used when projections of the joined tables are sorted on the join columns. Merge joins are faster and uses less memory than hash joins).

so Oracle do a full table scan of in outer table (EMPLOYEES) and the it read the inner table in a ordred manner.

the filtre predicates is the column on which the projection will be done

more details: https://datacadamia.com/db/oracle/merge_join

Answer 2

It uses the primary key to avoid sorting, otherwise the plan would be like this

The distinction between "Access predicates" and "Filter predicates" is not particularly consistent, so take them with healthy amount of skepticism. For example, if you remove the USE_MERGE hint, then there would be no Fiter Predicates in the plan any more, and the Access Predicates node would be relocated under the HASH_JOIN node (where it makes more sense for MERGE_JOIN as well):