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.