Re: MERGE vs REPLACE

Martijn,

Here is a quick test (Oracle 10.1.0.3/Linux):

SQL> select banner from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Prod
PL/SQL Release 10.1.0.3.0 - Production
CORE 10.1.0.3.0 Production
TNS for Linux: Version 10.1.0.3.0 - Production
NLSRTL Version 10.1.0.3.0 - Production

SQL> select * from merge_test_1;
ID NAME
---------- --------------------
1 aaa
2 bbb
3 ccc
4 ddd
5 eee
1 xxx
6 rows selected.

SQL> select * from merge_test_2;
ID NAME
---------- --------------------
1 AAA
2 BBB
6 FFF

SQL> select index_name from user_indexes where table_name like
'merge_test%';
no rows selected

SQL> merge into merge_test_1 a1
2 using merge_test_2 a2
3 on (a1.id = a2.id)
4 when matched then
5 update set a1.name = a2.name
6 when not matched then
7 insert (id, name) values (a2.id, a2.name);
4 rows merged.

SQL> select * from merge_test_1;
ID NAME
---------- --------------------
1 AAA
2 BBB
3 ccc
4 ddd
5 eee
1 AAA
6 FFF
7 rows selected.

Regards,
Lubomir Petrov

Martijn van Oosterhout wrote:
> 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,
>