Push_Subq

Another little warning for those about to upgrade from 9i to 10g. Some hints will change their behaviour. Consider the following SQL.

 
select  /*+ push_subq */ 
	par.small_vc1, 
	chi.small_vc1 
from 
	parent	par, 
	child	chi 
where 
	par.id1 between 1001 and 2200 
and	chi.id1 = par.id1 
and	exists ( 
		select 
			/*+ no_unnest qb_name(subq1) */ 
			null 
		from	subtest	sub1 
		where 
			sub1.id1       = par.id1 
		and	sub1.small_vc1 = par.small_vc1 
		and	sub1.small_vc2 >= '2' 
	) 
; 

By default, if Oracle cannot unnest a subquery it postpones executes of that subquery to the end of the execution plan.

Unhinted, Oracle will join parent to child then run the subquery. With the push_subq hint in place Oracle will acquire data from parent, run the subquery, then join any parent row that survives the subquery to child.

At least, that’s how it works until I get to 10g. 

Historically the push_subq hint told Oracle to run any outstanding subqueries at the earliest possible moment. But in 10g this changes (for the better). Imagine I have two subqueries in the query; it is quite possible that the optimum execution plan is to run one early and the other late, but the original implementation of push_subq is an ‘all or nothing’ implementation. 10g allows you to be selective about which subqueries should be pushed.

There are two options. I can simply move the push_subq hint into the subqueries that I want pushed, or I can use a ‘parameterized’ version of the push_subq hint in the main query block.

You will notice that I have used the qb_name() hint (a new 10g hint) in the subquery to give the subquery the name “subq1″. Because the query block is now named, I can reference it by name in other hints. So I can leave my existing push_subq hint where it is, but change it to read:

push_subq(@subq1)

(The “@” sign is necessary, not a typing error). If I have other subqueries that I want pushed I can create an individual hint for each of them, naming each in turn.

It doesn’t really matter which option you choose, I can think of good arguments for either, the important point is that if you are currently using the push_subq hint you will have to change your code when you upgrade to 10g.

Not surprisingly, the no_push_subq hint  (there are some subqueries that Oracle will automatically run early)  follows the same pattern. There may be others, but I haven’t gone looking for them yet.