PUSH_SUBQ hint

Recently, I discussed the hint from the title with Jonathan Lewis, no less. The performance tuning manual says the following:*************************************************************************************************************************************************************************************************

The PUSH_SUBQ hint instructs the optimizer to evaluate nonmerged subqueries at the earliest possible step in the execution plan. Generally, subqueries that are not merged are executed as the last step in the execution plan. If the subquery is relatively inexpensive and reduces the number of rows significantly, then evaluating the subquery earlier can improve performance.

This hint has no effect if the subquery is applied to a remote table or one that is joined using a merge join.