On Wed, 2008-04-30 at 16:58 +0100, Simon Riggs wrote:
> The main query will then look like this
>
> select target.ctid
> ,case when-not-matched (as above)
> ,case when-matched (as above)
> ,(all other columns required for side queries)
> from <source-query> left outer join <target> on <join-condition>
> where (<when-matched-condition-0>
> or <when-matched-condition-1>
> ...
> or <when-matched-condition-N>)
> or (<when-not-matched-condition-0>
> or <when-not-matched-condition-1>
> ...
> or <when-not-matched-condition-N>)
>
> The WHERE clause is likely required in case we get queries like this
>
> MERGE target t
> USING (select * from source) s
> ON (s.pkey = t.pkey)
> WHEN MATCHED AND s.pkey = $1
> UPDATE SET col = $2;
>
> which would be perfectly valid, even if we might hope that they had
> coded like this
>
> MERGE target
> USING (select * from source WHERE index-column = $1)
> ON (join-condition)
> WHEN MATCHED
> UPDATE SET col = $2;
Peter has just jogged my memory about double evaluation of volatile
functions, so the above transformation isn't correct.
We would not be able to fully optimise a MERGE statement like this
MERGE target t
USING (select * from source) sON (s.pkey = t.pkey)WHEN MATCHED AND s.key = $1 UPDATE SET col = $2;
since we won't be able to pass the clause "s.pkey = $1" down into the s
query so it would use an index. The following statement will be faster,
but will in all cases give an identical result:
MERGE target t
USING (select * from source WHERE key = $1) sON (s.pkey = t.pkey)WHEN MATCHED UPDATE SET col = $2;
I don't think its too important, since the latter is the way people
would have used MERGE in SQL:2003 anyway.
-- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com
