Oracle Scratchpad

October 30, 2006

Subquery Factoring

Filed under: Oracle,Performance,Subquery Factoring,Tuning — Jonathan Lewis @ 7:52 am GMT Oct 30,2006

A recent newsgroup question asked whether or not the “with subquery” clause – introduced as part of the select statement in 9i –  could also be used with the merge statement. The answer is yes, provided you remember that the merge statement  includes a select, and the subquery belongs to the select. The following, for example, is valid syntax:

        into old_data od
using   (
                with m_subq as (
                        from    new_data
                        where   mod(id,50) = 0
                select * from m_subq
        ) nd
on      (
         and    od.small_vc = nd.small_vc
when matched then
        update set od.padding = nd.padding
when not matched then
        insert values (, nd.small_vc, nd.padding )

Remember, before you get too excited by this option, that if the subquery is materialized, Oracle will create an “in memory metadata” global temporary table and write the result of the subquery to your temporary tablespace using direct path writes before reading it back with db file scattered reads.


The merge command projects all the columns from “old data” and “new data”, so if you’re only interested in a small subset of the columns then replacing table names with inline views that select only the relevant columns can make a difference.


  1. Jonathan, do you have any information on what influences the optimizer to write the intermediate result set to disk or not? I’m guessing that it’s some estimated number of bytes exceeding something like the sessions sort area size, but I’m just guessing there.

    Comment by David Aldridge — October 31, 2006 @ 8:26 pm GMT Oct 31,2006 | Reply

  2. David, As far as I am aware, you always get a dump to disc if the subquery is materialized – even if it’s only one row.
    It’s some time since I last tested this though, so it may be something that has (or will) change in the newer versions.

    Comment by Jonathan Lewis — October 31, 2006 @ 9:57 pm GMT Oct 31,2006 | Reply

  3. […] and we’re going to join it to itself, it would probably be a good idea to take advantage of subquery factoring to materialize the smallest result set before joining to the rest of the views. We might start […]

    Pingback by Internet SQL « Oracle Scratchpad — July 2, 2007 @ 12:28 am BST Jul 2,2007 | Reply

  4. […] to access the entire data set and produce a result table – either with a no_merge view, or a materialized factored subquery – is a good starting step – then join this result into the original […]

    Pingback by Functions « Oracle Scratchpad — July 9, 2007 @ 5:39 am BST Jul 9,2007 | Reply

  5. […] — Jonathan Lewis @ 8:24 pm UTC Jul 26,2007 I’ve written about subquery factoring a few times in the past and commented on the use of the /*+ materialize */ hint. Recently I had time to think […]

    Pingback by Subquery Factoring (2) « Oracle Scratchpad — July 19, 2007 @ 8:24 pm BST Jul 19,2007 | Reply

  6. […] have  to do things like introduce partial order through in-line views with no_merge hints, or subquery factoring with materialize hints to get the plan you […]

    Pingback by Full Hinting « Oracle Scratchpad — June 10, 2008 @ 3:50 pm BST Jun 10,2008 | Reply

  7. “using direct path writes before reading it back with db file scattered reads.”

    I did a test and it really did “db file scattered reads” to read back. Why doesn’t oracle read back using “direct path read” which will bypass SGA and use no latch?

    Comment by Daniel — August 4, 2009 @ 5:09 am BST Aug 4,2009 | Reply

    • Daniel,

      I approve of your desire to test and verify – so I’m only slightly upset that you didn’t take my word for it ;)

      The answer to your question is one that I often have to give to the many questions that people put to me about why Oracle does something in a certain way – I don’t know, I didn’t write the specification or the code.

      I can, however, speculate. If you assume that subquery factoring exists to allow large temporary results sets to be reusable within a query, then Oracle may have decided that writing to temp then reading to the buffer cache would be less of a threat to memory than creating and keeping the result set in the PGA.

      This, in many cases, is the difference between “with subquery” and deliberately introducing an inline non-mergeable view. (Although the materialized data can, of course, be used in multiple places in the same query, while the in-line view cannot).

      Comment by Jonathan Lewis — August 6, 2009 @ 4:39 pm BST Aug 6,2009 | Reply

    • Thanks for your good guess.

      The kind of IO oracle used surprised me at first, so I did a test to verify. Of course I takes your word, but anyone could have some typo, and I hope one day I can catch one of your typo :).

      Comment by Daniel — August 7, 2009 @ 8:37 am BST Aug 7,2009 | Reply

  8. […] may want to keep some of your old scripts handy for a while – you should think about using  subquery factoring – for example (with just a very simple and far from complete  plan […]

    Pingback by Plans in Memory « Oracle Scratchpad — February 11, 2010 @ 8:22 am GMT Feb 11,2010 | Reply

  9. […] 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:23 am BST Sep 16,2010 | Reply

  10. […] Subquery factoring and the merge command […]

    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: