Re: MERGE vs REPLACE

On Wed, Nov 23, 2005 at 12:24:24AM +0100, Petr Jelinek wrote:
> Btw about that keys, oracle gives error on many-to-one or many-to-many
> relationship between the source and target tables.

The standard has something called a "cardinality violation" if the
to-be-merged table doesn't match 1-1 with the rest of the statement. If
I had access to an Oracle I'd run two tests on MERGE:

1. Does the joining column have to have an index? For example, make a
column that's full of unique values but no unique index. According to
my reading of the the standard, this should still work (just slower).

2. Additionally, only the rows involved in the MERGE need to be
uniquely referenced, so if you add duplicate values but add a WHERE
clause to exclude those, it should also work.

My feeling is that requiring an index will limit it's usefulness as a
general tool.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.