Oracle Scratchpad

July 26, 2007

Subquery Factoring (2)

Filed under: CBO,Hints,Oracle,Performance,Subquery Factoring,Tuning — Jonathan Lewis @ 8:24 pm BST Jul 26,2007

I’ve written about subquery factoring (“with” subquery / CTE) a few times in the past and commented on the use of the /*+ materialize */ hint. Recently I had time to think about what it would take for the Cost Based Optimizer to decide to materialize a subquery without hinting.

I doubt if I have a complete answer yet, and I sometimes wonder if the optimizer code for handling subquery factoring is not yet complete, but my observations are as follows.

  • The cost (and I do mean the numbers you see in the execution plan output) of materializing a subquery should have three factors –
    • the cost of generating the subquery result set,
    • the cost of writing it to disc (which is done using direct path writes)
    • and the cost of scanning it back to use it (which is normally done using multiblock reads to cache)
  • The alternative to materializing is to copy the text into the query body and optimise it there. This leads to two options (and in 10g the optimizer will (generally) cost both options and take the cheaper):
    • either the subquery is merged,
    • or it is not merged.
  • If it is cheaper to leave the view unmerged then Oracle has to generate the subquery result set anyway – but doesn’t incur the cost of writing it and re-reading it (it goes into the PGA). This means that materializing will always be more expensive than putting the view inline – unless you use the the query result set at least twice in the query.

This is probably why I didn’t need to use a /*+ materialize */ hint in my example of re-writing a piece of bad “internet SQL” a few days ago – my factored subquery appeared twice in the final query.


Don’t use subquery factoring casually. Various people have reported examples where the simple presence of a factored subquery has disabled some feature of cost-based query transformation (CBQT) in 10gR2. In practice this means that the following two constructs may not end up using the same execution path – even if the optimizer decides to inline the factored subquery so that it is actually optimizing exactly the same code in both cases:

select  ...
from    (select ... from ...)

with factored_subq as (
        select ... from ...
select  ...
from    factored_subq

As a trivial example of the side-effects (or current restrictions, perhaps) of factored subqueries it is worth noting that it’s only in the most recent release of 10.2 that you can get a star transformation to work if the code has been embedded in a “with” clause. For other examples see the link to Randolf Geist’s blog in comment #4 below.

So be just a little bit careful with the technique – it’s very useful, very clean and tidy, and a good way to control difficult queries: but it may reduce the number of cunning tricks the optimizer can use.


  1. Hi Jonathan,
    in 92R2 I had some queries where the cost was less when using the materialize hint then not to use it.
    As far as I can remember the effect was that the CBO skiped hashjoins and uses NL insted.


    Comment by Wolfgang — July 20, 2007 @ 7:27 pm BST Jul 20,2007 | Reply

  2. Is there a alternative(documented) hint to materialize hint?

    Comment by Dharmendra — August 12, 2008 @ 8:53 pm BST Aug 12,2008 | Reply

  3. Dharmendra,

    None that I know of. If you have a copy of 11g, you could query view v$sql_hint for clues.

    Comment by Jonathan Lewis — August 19, 2008 @ 6:18 pm BST Aug 19,2008 | Reply

  4. I’ve been meaning to demonstrate a couple of queries where subquery factoring has changed the way the optimizer handles a query even when it puts the subquery back inline.

    Fortunately I’ve just discovered a couple of examples on Randolf Geist’s blog – so I can just point to that instead of writing my own.

    Comment by Jonathan Lewis — June 3, 2009 @ 8:49 pm BST Jun 3,2009 | Reply

  5. I’ve inherited a DW setup with views across database link with the view the DW side to 4 ERP tables. Recently they moved ERP 300 miles away from the DW server and wondered why performance degraded. Looking at the plans we were seeing multiple REMOTE operations with NL/HASH etc.. being carried out on the DW server. I attempted to ‘materialize’ the view using the hint so the view joins would be carried out on the remote server with just the result set returned (and subsequent joins to local tables). This didn’t work 100% of the time, also attempted using rownum as this has the effect of materializing the set, but this again didn’t work 100% of the time. Subquery Factoring was then attempted, and so far so good, the remote joins have disappeared, we now just have one REMOTE operation with the result set (still not ideal), but at least the data returns in a reasonable time.

    Comment by Matt — November 23, 2009 @ 9:30 pm GMT Nov 23,2009 | Reply

    • Matt,

      I may be reading your comment incorrectly, but when you talk about materializing a view using a hint, I guess you mean the ‘materialize’ hint: however, this only applies to subquery factoring and materializes the factored subquery.

      Using subquery factoring to factor out and isolate the remote join is a good strategy, though.

      An alternative strategy is to put the remote join into an inline view with the /*+ no_merge */.

      In some cases you can go one better and simply stack in a complete set of hints to control the join order and every join method so that you get the optimum compromise of remote joins and network traffic – without using either method of “ring-fencing” the remote bit.

      Comment by Jonathan Lewis — November 25, 2009 @ 1:29 pm GMT Nov 25,2009 | Reply

  6. Thanks Jonathan, yes an attempt was made to /*+ materialize */ a standard non subquery factored view, which I guess is why it wasn’t working!

    Long term adding the hints to control the join order without compromising the network traffic will be the goal, but SQF provided a quick win to a production issue.

    Comment by Matt — November 25, 2009 @ 9:44 pm GMT Nov 25,2009 | Reply

  7. […] Filed under: Uncategorized — Jonathan Lewis @ 6:28 pm UTC Jun 29,2010 From time to time I’ve warned people that subquery factoring should be used with a little care if all you’re trying to do is make a query more readable by […]

    Pingback by Subquery Factoring (3) « Oracle Scratchpad — June 29, 2010 @ 11:11 pm BST Jun 29,2010 | Reply

  8. […] Articles: Complex View Merging Subquery Factoring 1 Subquery Factoring 2 Subquery Factoring (3) Pushing Predicates (1) Pushing Predicates […]

    Pingback by Subquery Factoring (4) « Oracle Scratchpad — September 16, 2010 @ 9:24 am BST Sep 16,2010 | Reply

  9. […] you can see, Oracle has copied the two factored subqueries inline (they appear just once each in the body of the query so this is – probably – inevitable). Then Oracle has obeyed […]

    Pingback by ANSI – argh « Oracle Scratchpad — December 3, 2010 @ 7:32 pm GMT Dec 3,2010 | Reply

  10. […] not aware of this threshold being documented but Jonathan Lewis mentioned this observation here and it ties in with what I’ve […]

    Pingback by Materialize « OraStory — January 17, 2012 @ 10:48 am GMT Jan 17,2012 | Reply

  11. […] the last thre or four years I’ve made several commentsabout how subquery factoring could result in changes in execution plans even if the […]

    Pingback by Subquery Factoring « Oracle Scratchpad — February 1, 2012 @ 5:53 pm GMT Feb 1,2012 | Reply

  12. […] materialized, no matter how you applied the /*+ inline */ hint. The CTE seemed to be following the basic guideline for CTEs – if you use it once in the main query it goes inline, if you use it more than once it will […]

    Pingback by Recursive subquery factoring | Oracle Scratchpad — February 16, 2014 @ 6:11 pm GMT Feb 16,2014 | Reply

  13. […] years go (eight to be precise) I wrote a note suggesting that Oracle will not materialize a factored subquery unless it is used at least twice in […]

    Pingback by Subquery Factoring (9) | Oracle Scratchpad — July 24, 2015 @ 12:34 pm BST Jul 24,2015 | Reply

  14. […] When does Oracle materialize a “with” subquery automatically […]

    Pingback by CTE Catalogue | Oracle Scratchpad — June 10, 2020 @ 6:46 pm BST Jun 10,2020 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

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

You are commenting using your 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.

Website Powered by

%d bloggers like this: