Predicates that we commonly use are Equality operators (=, <>, >, <, …), BETWEEN, IN, NOT, LIKE, EXIST and many more. Do you know that there is a processing order for each predicate. That is, at what stage of the SQL Query execution, particular predicate is applied.
Predicates are applied by stage in the following order:
- Indexable predicates that match on index key columns are applied and evaluated when the index is accessed.
- Stage 1 index screening predicates that have not been picked as index matching predicates but still refer to index columns, are applied to the index.
- Stage 1 page range screening predicates refer to partitioning columns are applied to limit the number of partitions that are accessed.
- Other stage 1 predicates are applied to the data, after data page access.
- The stage 2 predicates are applied on the returned data rows.
And list of all predicates and above information can be found at the IBM link