PUSH_PRED and NO_PUSH_PRED Hints

Introduction

In this article, we explore the PUSH_PRED hint as part of our series on Oracle hints. The PUSH_PRED hint tells the Oracle optimizer to push predicates from an outer query block into a view or inline view (subquery). This allows filtering to happen earlier in the execution plan, potentially reducing the volume of data processed in downstream operations. Used appropriately, this hint can result in significant performance improvements, especially when dealing with complex views or subqueries.

However, predicate pushing is not always automatically done by Oracle. This is particularly the case where query rewriting might alter semantics or when cost-based calculations favor deferring predicates. The PUSH_PRED hint gives developers and DBAs more control by explicitly instructing Oracle to apply predicate pushing. Forcing predicate pushdown can lead to unintended side effects, including incorrect results if used carelessly with views containing aggregation or DISTINCT, so as always, test, test, test.

 How Oracle Handles Predicate Pushing 

Oracle’s query optimizer may choose to delay the application of outer predicates in certain contexts.  Below are some of the common situations

• Complex views or inline subqueries

• Views with grouping, joins, or aggregation

• Materialized views or query rewrite scenarios

When predicates are applied after the view is materialized (i.e., fully executed), it can result in large intermediate result sets that are filtered too late. By contrast, pushing the predicates directly into the view can minimize I/O and memory usage by filtering early.  With this in mind, the PUSH_PRED hint overrides the optimizer’s decision and attempts to push those filters into the view, allowing early pruning of rows. However, the hint may be ignored, if Oracle believes it logically unsafe to apply it.  That said, Oracle does not always get that call right, and as mentioned above, you can get inconsistent results.  This is not a bug, but rather a consequence of what you are asking Oracle to do.  e.g. Change the semantics of the query.

Notes

• The PUSH_PRED hint instructs Oracle to move outer query predicates into the FROM clause view or inline subquery.

• It is most useful when filtering can reduce the number of rows early in the execution.

• The hint requires a view alias, making its syntax clear and specific.

• It does not apply if the subquery contains constructs like DISTINCT, GROUP BY, or CONNECT BY unless Oracle determines it's semantically safe. (caveat above applies)

• There is a corresponding NO_PUSH_PRED hint if you want to prevent predicate pushing.

Using the PUSH_PREDHint Example

SELECT /*+ PUSH_PRED(v) */ *

  FROM (SELECT * FROM employees WHERE department_id IS NOT NULL) v

 WHERE v.salary > 10000

/

In this example, the outer query includes a filter (v.salary > 10000) that applies after the subquery. The PUSH_PRED hint tells Oracle to push this filter into the subquery, enabling early filtering of rows based on salary before materializing the view v. 

Example Plan

Plan hash value: 1234567890

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

| Id  | Operation                     | Name               | Rows  | Bytes | Cost  |

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

|   0 | SELECT STATEMENT              |                    |   50  |  1000 |     4 |

|   1 |  VIEW                         |                    |   50  |  1000 |     4 |

|*  2 |   TABLE ACCESS BY INDEX ROWID | EMPLOYEES          |   50  |       |     3 |

|*  3 |    INDEX RANGE SCAN           | EMP_SALARY_IDX     |   50  |       |     2 |

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

Predicate Information (identified by operation id):

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

   2 - filter("SALARY">10000 AND "DEPARTMENT_ID" IS NOT NULL)

   3 - access("SALARY">10000)

Notes

1. The outer query is performing a simple SELECT.

2. Represents the inline view (v). This is where the optimizer will try to push the filter v.salary > 10000 into the subquery.

3. Oracle is using an index to access rows in the EMPLOYEES table.

4. The EMP_SALARY_IDX index is used to efficiently find employees with SALARY > 10000. This means the predicate was pushed down into the subquery (as per the hint), and Oracle is using the index to fetch only the relevant rows.

Considerations

While PUSH_PRED can improve performance, it must be used thoughtfully

• Be careful when pushing predicates into views that include aggregation, joins, or set operations. You could inadvertently change the results.

• Always compare execution plans and actual performance (with and without the hint).

• Predicate pushing can obscure query logic in complex queries. Document your intent clearly.

• If using views, consider whether the view itself should include all necessary filters, or whether flexible outer filtering makes more sense.

NO_PUSH_PRED Hint

The NO_PUSH_PRED hint prevents Oracle from pushing a predicate into a view or subquery. This may be useful when pushing would lead to a performance degradation (e.g., when pushing a filter breaks partition pruning or disables a merge join). 

Further Reading

• You can find more information on using the hint in the documentation 

Published on 

Published 7th May 2025