Subquery Factoring (2)

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.

Warning:

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.