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