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.

[Further reading on "subquery factoring"]



  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 GMT 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 GMT 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 GMT 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 GMT 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 GMT Aug 4,2009 | Reply

    • Daniel,

      I approve of you desire to test an 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 – I don’t know, I didn’t write the specification or the code.

      However, I can 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 GMT 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 GMT 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 GMT Sep 16,2010 | Reply

RSS feed for comments on this post. TrackBack URI

Leave a Reply

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

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

Google+ photo

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

Connecting to %s

Theme: Rubric. Get a free blog at


Get every new post delivered to your Inbox.

Join 4,308 other followers