Query Tuning 101: Access vs. Filter Predicates In Execution Plans

Have you ever wondered what the difference is between the "filter predicates" or "access predicates" steps listed in execution plans - or even what a predicate is?

If so, watch this video to find out!

Copyright © 2015 Oracle and/or its affiliates. Oracle is a registered trademark of Oracle and/or its affiliates. All rights reserved. Other names may be registered trademarks of their respective owners. Oracle disclaims any warranties or representations as to the accuracy or completeness of this recording, demonstration, and/or written materials (the “Materials”). The Materials are provided “as is” without any warranty of any kind, either express or implied, including without limitation warranties or merchantability, fitness for a particular purpose, and non-infringement.

[josemiguelgonzalezayala]

If I have an index with 3 columns and in the WHERE condition I search for the first and third columns, how is it possible to access the first and third columns if it is a B-Tree index?

For example, index = C1, C2, C3

WHERE, C1='X' and C3='Z''.

Output of the Explain plan:

2- access("C1"='X' AND "C3"='Z')
filter("C3"='Z')

All in one operation (2)

[wojciechkorzenny]

Hey,

I have a question regarding this.
Let's take following example (based on well-known HR sample schema):

select email
from employees
where email like 'A%';

Explain plan for this query is:


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

| 0 | SELECT STATEMENT | | 11 | 88 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| I_EMP_EMAIL_LN_FN | 11 | 88 | 1 (0)| 00:00:01 |


Predicate Information (identified by operation id):


1 - access("EMAIL" LIKE 'A%')
filter("EMAIL" LIKE 'A%')


Why are there two predicate information if there is only one WHERE condition? Especially that according to what you said in video, one is for fetching rows which match condition (access) and another (filter) discards not matching ones (but after "access"-ing such shouldn't exist).