Subquery Factoring

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:

merge
        into old_data od
using   (
                with m_subq as (
                        select
                                *
                        from    new_data
                        where   mod(id,50) = 0
                )
                select * from m_subq
        ) nd
on      (
                od.id = nd.id
         and    od.small_vc = nd.small_vc
        )
when matched then
        update set od.padding = nd.padding
when not matched then
        insert values ( nd.id, 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.

Footnote

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.