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
seta1.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,
>