Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How come the same predicate is performed both access and filter?

unknown-879931Dec 5 2013 — edited Dec 7 2013

SELECT e.last_name, j.job_title, d.department_name

FROM hr.employees e, hr.departments d, hr.jobs j

WHERE e.department_id = d.department_id

AND e.job_id = j.job_id

AND e.last_name LIKE 'A%' ;

Execution Plan

----------------------------------------------------------

Plan hash value: 975837011

---------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

---------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 3 | 189 | 7 (15)| 00:00:01 |

|* 1 | HASH JOIN | | 3 | 189 | 7 (15)| 00:00:01 |

|* 2 | HASH JOIN | | 3 | 141 | 5 (20)| 00:00:01 |

| 3 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 3 | 60 | 2 (0)| 00:00:01 |

|* 4 | INDEX RANGE SCAN | EMP_NAME_IX | 3 | | 1 (0)| 00:00:01 |

| 5 | TABLE ACCESS FULL | JOBS | 19 | 513 | 2 (0)| 00:00:01 |

| 6 | TABLE ACCESS FULL | DEPARTMENTS | 27 | 432 | 2 (0)| 00:00:01 |

---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

  1 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

  2 - access("E"."JOB_ID"="J"."JOB_ID")

  4 - access("E"."LAST_NAME" LIKE 'A%')

     filter("E"."LAST_NAME" LIKE 'A%')

I don't understand that how come E.LAST_NAME column is evaluated both access and filter? Can somebody please explain this? Or recommend me a article or document that explain what excatly predicate explains in explain plan?

Thanks in advance.

This post has been answered by Sudhakar_B on Dec 6 2013
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jan 4 2014
Added on Dec 5 2013
18 comments
2,177 views