Oracle Scratchpad

March 9, 2007


Filed under: CBO,Execution plans,Hints,Troubleshooting,Tuning — Jonathan Lewis @ 2:08 pm GMT Mar 9,2007

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 */ 
	parent	par, 
	child	chi 
	par.id1 between 1001 and 2200 
and	chi.id1 = par.id1 
and	exists ( 
			/*+ no_unnest qb_name(subq1) */ 
		from	subtest	sub1 
			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:


(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.


  1. Jonathan, is the old “/*+ push_subq */” (without any argument) ignored in 10g ? I haven’t understood whether the new syntax is mandatory or simply a fine-grained additional variation. Thanks!

    Comment by Alberto Dell'Era — March 10, 2007 @ 9:44 am GMT Mar 10,2007 | Reply

  2. I’ve noticed this change with push_subq some time ago and i was using the method with push_subq used in subquery. But method with naming subquery and referencing it name in other hint is nice solution.

    Comment by Paweł Barut — March 10, 2007 @ 4:53 pm GMT Mar 10,2007 | Reply

  3. Alberto, the old syntax no longer works.

    Comment by Jonathan Lewis — March 11, 2007 @ 7:09 am GMT Mar 11,2007 | Reply

  4. Hi

    I have a question about push_subq.

    I can see that it works as explained with exist.doest it work ,in case of NOT EXISTS as well.

    I have a query like this – returns 14 rows,takes almost 90 mins.

    SELECT /*+   ordered  use_hash(mtp) push_subq */ ooha.org_id "OPERATING UNIT ID"
    FROM        apps.oe_transaction_types_all ott,
                apps.oe_transaction_types_tl ottt_l,
                apps.oe_order_lines_all oola,
                apps.mtl_parameters mtp,
                apps.oe_order_headers_all ooha,
                apps.hz_cust_accounts hca
    WHERE  ooha.header_id = oola.header_id AND
           ooha.org_id = oola.org_id AND
           ooha.order_source_id = 1263 AND
           ott.transaction_type_id = ottt_l.transaction_type_id AND
           ott.org_id = ooha.org_id AND
           oola.line_type_id = ottt_l.transaction_type_id AND
           oola.line_type_id = ott.transaction_type_id AND
           mtp.organization_id = oola.ship_from_org_id AND
           hca.cust_account_id = ooha.sold_to_org_id AND
           oola.flow_status_code IN ('SHIPPED', 'CLOSED') AND
           hca.attribute9 IS NOT NULL
           and NOT EXISTS
           ( SELECT /*+ NO_UNNEST */ 1
           FROM   apps.mtl_material_transactions mmt ,
                  apps.mtl_transaction_lot_numbers mtnl ,
                  apps.mtl_parameters mp ,
                  apps.mtl_item_locations mil
           WHERE  mmt.inventory_item_id = oola.inventory_item_id AND
                  mmt.transaction_type_id IN (420, 40, 41, 42) AND
                  mmt.transaction_quantity > 0 AND
                  mtnl.lot_number = ooha.order_number AND
                  mtnl.transaction_id = mmt.transaction_id AND
                  mmt.transaction_date > (TRUNC(SYSDATE) -90) AND
                  mmt.organization_id = mp.organization_id AND
                  mp.attribute7 = 'PARTS' AND
                  mp.attribute11 ='SERVICE' AND
                  mil.inventory_location_id = hca.attribute9 AND
                  mil.organization_id = mp.organization_id
           ) AND
           NVL(oola.actual_shipment_date, oola.schedule_ship_date) > SYSDATE - 90
    its tkprof - 
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.00       0.00          0          0          0           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch        2    690.28    4899.16    1524771   23494672          0          12
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        4    690.28    4899.16    1524771   23494672          0          12
    Misses in library cache during parse: 0
    Optimizer goal: CHOOSE
    Parsing user id: 65  (APPS)
    Rows     Row Source Operation
    -------  ---------------------------------------------------
         12  NESTED LOOPS  (cr=23494672 r=1524771 w=0 time=4640213970 us)
     157696   NESTED LOOPS  (cr=5730245 r=1402270 w=0 time=3699650845 us)
     157697    HASH JOIN  (cr=5257151 r=1379549 w=0 time=3576187058 us)
     157697     TABLE ACCESS BY INDEX ROWID OE_ORDER_LINES_ALL (cr=5257149 r=1379549 w=0 time=3575115599 us)
    2789657      NESTED LOOPS  (cr=13232 r=12982 w=0 time=90323177 us)
         76       HASH JOIN  (cr=277 r=82 w=0 time=175347 us)
       4597        TABLE ACCESS FULL OE_TRANSACTION_TYPES_ALL (cr=157 r=0 w=0 time=6683 us)
         76        TABLE ACCESS FULL OE_TRANSACTION_TYPES_TL (cr=120 r=82 w=0 time=157808 us)
    2789580       INDEX RANGE SCAN GEMS_ONT_ORDER_LINES_N99 (cr=12955 r=12900 w=0 time=86301122 us)(object id 1863725)
        377     INDEX FULL SCAN MTL_PARAMETERS_U1 (cr=2 r=0 w=0 time=367 us)(object id 9847)
     157696    TABLE ACCESS BY INDEX ROWID OE_ORDER_HEADERS_ALL (cr=473094 r=22721 w=0 time=123022055 us)
     157697     INDEX UNIQUE SCAN OE_ORDER_HEADERS_U1 (cr=315397 r=1363 w=0 time=11565697 us)(object id 688729)
         12   TABLE ACCESS BY INDEX ROWID HZ_CUST_ACCOUNTS (cr=17764427 r=122501 w=0 time=1114177945 us)
     157696    INDEX UNIQUE SCAN HZ_CUST_ACCOUNTS_U1 (cr=315395 r=274 w=0 time=3770326 us)(object id 715003)
     157621    TABLE ACCESS BY INDEX ROWID MTL_TRANSACTION_LOT_NUMBERS (cr=17291336 r=121205 w=0 time=1096619120 us)
    6897874     NESTED LOOPS  (cr=14562731 r=114663 w=0 time=1018837983 us)
    3448931      NESTED LOOPS  (cr=7488435 r=112057 w=0 time=936487677 us)
     157633       NESTED LOOPS  (cr=788165 r=581 w=0 time=10022817 us)
     157633        INDEX RANGE SCAN MTL_ITEM_LOCATIONS_U1 (cr=472899 r=581 w=0 time=7984520 us)(object id 9761)
     157633        TABLE ACCESS BY INDEX ROWID MTL_PARAMETERS (cr=315266 r=0 w=0 time=1667395 us)
     157633         INDEX UNIQUE SCAN MTL_PARAMETERS_U1 (cr=157633 r=0 w=0 time=770942 us)(object id 9847)
    3448931       TABLE ACCESS BY INDEX ROWID MTL_MATERIAL_TRANSACTIONS (cr=6700270 r=111476 w=0 time=924106849 us)
    8273225        INDEX RANGE SCAN MTL_MATERIAL_TRANSACTIONS_N1 (cr=695175 r=32781 w=0 time=303092226 us)(object id 6205511)
    3448931      INDEX RANGE SCAN MTL_TRANSACTION_LOT_NUMBERS_N1 (cr=7074296 r=2606 w=0 time=74416944 us)(object id 9976)

    – my questiones are:

    how does push_subq will help or rathe is it helping or is it a valid choice here.

    the tables in NOT EXISTS have lots of data.

    is there a way I can improve the performance of this query.


    Comment by Ajeet — April 4, 2008 @ 2:24 pm BST Apr 4,2008 | Reply

  5. Ajeet,

    Sorry, the supplied data is not formatted for easy reading. See the link to “how to comment” at top-right if you want to try again. [Now fixed – jl]

    The only thing I will say is that push_subq applies to subqueries that are not ‘unnested’ – which means that appear in the execution plans as filter operations. I didn’t see an explicit filter in your execution plan – but sometimes the filter operation is not displayed.

    Comment by Jonathan Lewis — April 16, 2008 @ 7:11 pm BST Apr 16,2008 | Reply

  6. Hi Jonathan

    I read about query transformation in your book.
    I could not uderstand why the query below is not using HASH_AJ for the not exists subquery .

    the query below runs for 1 hr to return 50K rows.

    could you please suggest what is wrong here –

      '|' ||POH.REVISION_NUM ||'|' ||POH.CLOSED_CODE || '|' ||POL.LINE_NUM ||'|'
      ||GCC.SEGMENT1||'|' ||GCC.SEGMENT2||'|' ||GCC.SEGMENT3||'|'
      ||GCC.SEGMENT4||'|' ||GCC.SEGMENT5||'|' ||GCC.SEGMENT6||'|'
      ||GCC.SEGMENT7||'|' ||GCC.SEGMENT8||'|' ||GCC.SEGMENT9||'|'
      ||PDA.QUANTITY_DELIVERED || '|' ||POLL.NEED_BY_DATE || '|' ||NULL || '|'
      ||NULL || '|' ||NULL || '|' ||PDA.DISTRIBUTION_NUM || '|'
      || '|' ||POLL.QTY_RCV_TOLERANCE || '|' ||'|' ||'|' ||'|' ||'|' ||'|' ||'|'
      ||'|' ||'|' ||'|' ||'|' ||'|' PO_RECON_DATA_CUR FROM T3 POH ,
      (SELECT /*+ HASH_AJ(apps.ap_invoice_distributions_all)*/ 1 FROM
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.14       0.12          0          0          0           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch    53777    431.65    4146.97     849561   15077239          0       53776
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total    53779    431.79    4147.10     849561   15077239          0       53776
    Misses in library cache during parse: 1
    Optimizer goal: CHOOSE
    Parsing user id: 65  (APPS)   (recursive depth: 1)
    Rows     Row Source Operation
    -------  ---------------------------------------------------
      53776  FILTER  (cr=14968161 r=830308 w=0 time=3992044684 us)
     857292   NESTED LOOPS  (cr=12395792 r=765116 w=0 time=3469343008 us)
     857292    NESTED LOOPS  (cr=9770138 r=746321 w=0 time=3257402729 us)
     817106     NESTED LOOPS  (cr=7122874 r=527908 w=0 time=2163647611 us)
     815224      NESTED LOOPS  (cr=4467909 r=254484 w=0 time=873769330 us)
     815224       NESTED LOOPS  (cr=3600016 r=254484 w=0 time=867378738 us)
     815224        NESTED LOOPS  (cr=1916899 r=254484 w=0 time=854167408 us)
     536644         HASH JOIN  (cr=67037 r=20961 w=0 time=23132035 us)
     265974          TABLE ACCESS FULL PO_VENDOR_SITES_ALL (cr=16203 r=13803 w=0 time=8342317 us)
     536644          HASH JOIN  (cr=50834 r=7158 w=0 time=10949413 us)
     146208           TABLE ACCESS FULL PO_VENDORS (cr=7332 r=0 w=0 time=156064 us)
     536644           TABLE ACCESS FULL T3 (cr=43502 r=7158 w=0 time=7288830 us)
     815224         TABLE ACCESS BY INDEX ROWID PO_LINES_ALL (cr=1849862 r=233523 w=0 time=828571445 us)
     815224          INDEX RANGE SCAN PO_LINES_U2 (cr=1169044 r=61552 w=0 time=254156446 us)(object id 10182)
     815224        TABLE ACCESS BY INDEX ROWID MTL_CATEGORIES_B (cr=1683117 r=0 w=0 time=10259002 us)
     815224         INDEX UNIQUE SCAN MTL_CATEGORIES_B_U1 (cr=867893 r=0 w=0 time=4879455 us)(object id 9632)
     815224       INDEX UNIQUE SCAN MTL_CATEGORIES_TL_U1 (cr=867893 r=0 w=0 time=4024518 us)(object id 684692)
     817106      TABLE ACCESS BY INDEX ROWID PO_LINE_LOCATIONS_ALL (cr=2654965 r=273424 w=0 time=1285786829 us)
     817106       INDEX RANGE SCAN PO_LINE_LOCATIONS_N2 (cr=1744948 r=71462 w=0 time=361371836 us)(object id 10194)
     857292     TABLE ACCESS BY INDEX ROWID PO_DISTRIBUTIONS_ALL (cr=2647264 r=218413 w=0 time=1089673131 us)
     857292      INDEX RANGE SCAN PO_DISTRIBUTIONS_N1 (cr=1748737 r=75456 w=0 time=367235456 us)(object id 10127)
     857292    TABLE ACCESS BY INDEX ROWID GL_CODE_COMBINATIONS (cr=2625654 r=18795 w=0 time=208731765 us)
     857292     INDEX UNIQUE SCAN GL_CODE_COMBINATIONS_U1 (cr=1768362 r=3248 w=0 time=41687174 us)(object id 51426)
     803516   INDEX RANGE SCAN AP_INVOICE_DISTRIBUTIONS_N7 (cr=2572369 r=65192 w=0 time=516473241 us)(object id 7199)

    database version is oracle 9iR2.

    Comment by Ajeet — May 7, 2008 @ 2:42 pm BST May 7,2008 | Reply

  7. Ajeet,

    I pointed out that your last commented hadn’t been formatted for readability – and the same applies to this one. [Now fixed – jl]

    Ignoring the huge mess of the query and tkprof output, though, your question is “why hasn’t Oracle changed a “not exists” subquery to an anti-join.

    You don’t need a big example to investigate this. A small example with just two tables will let you investigate that one detail and then allow you to apply the concept to more complex cases.

    Off the top of my head, there are probably only two things to investigate. (a) maybe it’s the arithmetic, (b) maybe it relates to the need for “not null” constraints on the correlating columns. (And (a2) if you want to hint it, then checking that you’ve definitely used the hints correctly).

    Comment by Jonathan Lewis — May 8, 2008 @ 7:41 am BST May 8,2008 | Reply

  8. Jonathan

    adding a not null constraint in the subquery has indeed converted that into hash anti join,
    infact in the “Cost based fundamentals” also you have written – but i was confused as it was discussed for NOT IN.
    sorry about poor formating.
    I will pay attention on this in future postings.


    Comment by Ajeet — May 13, 2008 @ 7:15 am BST May 13,2008 | Reply

  9. […] push_subq: how upgrades can stop hints working. […]

    Pingback by Rules for Hinting « Oracle Scratchpad — December 2, 2010 @ 8:57 am GMT Dec 2,2010 | Reply

RSS feed for comments on this post. TrackBack URI

Leave a Reply to Ajeet Cancel reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by