Push_Subq

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

 
rem
rem     Script:  push_subq.sql
rem     Author:  Jonathan Lewis
rem     Dated:   Sept 2003
rem

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 execution of that subquery to the end of the execution plan. I’ve used the no_unnest hint in the subquery to stop Oracle from unnesting it.

In the absence of the push_subq hint Oracle would join parent to child then run the subquery. With the push_subq hint in place Oracle will acquire data from parent, run the subquery (which correlates to the parent), 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 ‘all or nothing’. 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 each of 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 other hints that have changed in similar ways, but I haven’t gone looking for them yet.

Footnote

Execution plan with “pushed” subquery. (See also my reply to comment 10 below for a detailed explanation of reading the plan – which includes the predicate, object/alias, and outline sections from 12cR2)

-------------------------------------------------------------------------
| Id  | Operation                     | Name    | Rows  | Bytes | Cost  |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |   139 |  2502 |   101 |
|*  1 |  HASH JOIN                    |         |   139 |  2502 |    99 |
|*  2 |   TABLE ACCESS FULL           | PARENT  |    50 |   450 |    20 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| SUBTEST |     1 |    14 |     2 |
|*  4 |     INDEX UNIQUE SCAN         | SUB_PK  |     1 |       |     1 |
|*  5 |   TABLE ACCESS FULL           | CHILD   |  8011 | 72099 |    78 |
-------------------------------------------------------------------------

Execution plan with a no_push_subq hint in the subquery

------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost  |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     1 |    18 |  2881 |
|*  1 |  FILTER                      |         |       |       |       |
|*  2 |   HASH JOIN                  |         |  2781 | 50058 |    99 |
|*  3 |    TABLE ACCESS FULL         | PARENT  |  1001 |  9009 |    20 |
|*  4 |    TABLE ACCESS FULL         | CHILD   |  8011 | 72099 |    78 |
|*  5 |   TABLE ACCESS BY INDEX ROWID| SUBTEST |     1 |    14 |     2 |
|*  6 |    INDEX UNIQUE SCAN         | SUB_PK  |     1 |       |     1 |
------------------------------------------------------------------------