PUSH_SUBQ and NO_PUSH_SUBQ Hints

Introduction

In this article, we explore the Oracle SQL hint PUSH_SUBQ as part of our series on Oracle hints. The PUSH_SUBQ hint instructs Oracle to attempt to push a subquery’s execution earlier in the execution plan. Specifically, Oracle will try to push the subquery into the outer query’s join operation, which can improve performance by reducing the need for intermediate results or redundant data retrievals. This hint is particularly useful when dealing with subqueries that can be evaluated more efficiently in the context of the outer query.

Like many other hints, the PUSH_SUBQ hint should be used carefully. Overuse or improper use may lead to suboptimal performance due to excessive data retrieval or unnecessary complexity in the execution plan. As always test, test, test.

Before we dive into an example of the PUSH_SUBQ hint, let’s review how Oracle executes subqueries in a typical query.

 How Oracle Executes Subqueries

Subqueries are typically executed in one of two ways 

1. As a correlated subquery 

2. As a non-correlated subquery. 

Oracle determines the method of execution based on several factors, including the structure of the query and the cost of different approaches.

Correlated Subqueries

A correlated subquery references columns from the outer query, and its execution depends on the rows processed by the outer query. These subqueries are typically executed once for each row of the outer query.

Non-Correlated Subqueries 

A non-correlated subquery does not reference the outer query. It can often be executed independently of the outer query, which can be more efficient, as Oracle can evaluate the subquery only once.

In both cases, Oracle has various methods of optimizing subquery execution, including materializing intermediate results or transforming the subquery into a join. The PUSH_SUBQ hint plays a role in guiding Oracle’s optimizer to push subqueries to more efficient locations in the query plan.  

Using the PUSH_SUBQ Hint Example

SELECT /*+ PUSH_SUBQ */

    e.employee_id,

    e.first_name,

    e.last_name

FROM employees e

WHERE e.department_id IN (

    SELECT d.department_id

    FROM departments d

    WHERE d.location_id = 1700

);

In this example, the PUSH_SUBQ hint suggests to Oracle that it should try to evaluate the subquery first, filtering departments before joining with employees.

Example Plan

Plan hash value: 987654321

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

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

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

|   0 | SELECT STATEMENT                    |               |   100 |  3000 |   15 | 00:00:01 |

|   1 |  HASH JOIN                          |               |   100 |  3000 |   15 | 00:00:01 |

|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| DEPARTMENTS  |    10 |   120 |    3 | 00:00:01 |

|   3 |    INDEX RANGE SCAN                 | DEPT_LOC_IDX  |    10 |       |    1 | 00:00:01 |

|   4 |   TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES    |   100 |  2000 |   12 | 00:00:01 |

|   5 |    INDEX RANGE SCAN                 | EMP_DEPT_IDX  |   100 |       |    4 | 00:00:01 |

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

Notes

Step 1: HASH JOIN

1. • The results from the subquery (department IDs) and the employees table are joined using a hash join, which is efficient for matching the small set of department_id values from the subquery against the employees table.

Step 2: TABLE ACCESS BY INDEX ROWID BATCHED

1. • Oracle fetches the corresponding department_id values from the departments table using the ROWIDs obtained from the index scan. Batching reduces I/O overhead.

Step 3: INDEX RANGE SCAN on DEPT_LOC_IDX

1. • The subquery (SELECT d.department_id FROM departments d WHERE d.location_id = 1700) uses the index DEPT_LOC_IDX to quickly identify rows in departments where location_id = 1700. This is efficient because location_id is selective (e.g., only a few departments have location_id = 1700).

   • The optimizer evaluates this subquery early due to the PUSH_SUBQ hint, ensuring the department IDs are retrieved before accessing employees.

Step 4: TABLE ACCESS BY INDEX ROWID BATCHED

• • Oracle retrieves the employee_id, first_name, and last_name columns from the employees table using ROWIDs from the index scan.

Step 5: INDEX RANGE SCAN on EMP_DEPT_IDX

1. • The outer query filters employees where department_id matches the subquery’s results. The index EMP_DEPT_IDX on department_id allows Oracle to efficiently locate the relevant employee rows.

Considerations

Before applying the PUSH_SUBQ hint, you might consider the following alternatives:

• In some cases, restructuring the subquery as a join might be more efficient, especially if the subquery can be rewritten to avoid the need for pushing.

• Ensure that the tables involved have appropriate indexes. Indexing the columns used in the subquery and the outer query’s join conditions can further optimize the query.

NO_PUSH_SUBQ Hint

The NO_PUSH_SUBQ hint directs the optimizer to delay the evaluation of non-merged subqueries until the final step of the execution plan. This approach can be beneficial when the subquery is costly to execute or offers minimal filtering benefit, as it avoids processing it unnecessarily early. 

Further Reading

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

Published on 

Published 7th May 2025