Oracle Scratchpad

September 13, 2010

Subquery Factoring (4)

Filed under: CBO,Oracle,Subquery Factoring,Troubleshooting,Tuning — Jonathan Lewis @ 6:38 pm BST Sep 13,2010

I’ve written before about the effects of subquery factoring (common table expressions – or CTEs) on the optimizer, and the way that the optimizer can “lose” some strategies when you start factoring out subquery expressions. Here’s another example I came across quite recently. It involved a join of about 15 tables so I’ve only extracted a few lines from the SQL and resulting execution plans.

We start with the original query, which had factored out an aggregate subquery then used it in place of an inline view:

June 29, 2010

Subquery Factoring (3)

Filed under: CBO,Execution plans,Oracle,Performance,Subquery Factoring,Troubleshooting — Jonathan Lewis @ 6:28 pm BST 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 extracting parts of the SQL into “factored subqueries” (or Common Table Expressions – CTEs – if you want to use the ANSI term for them). In principle, for example, the following two queries should produce the same  execution plan:

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.

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.

« Previous Page

Website Powered by