That isn't a problem - it is a FACT.

If the join key ISN'T unique in the table you join to - then the update is not deterministic (means there would be more than one row X gets joined to in Y).

Hence the need for the primary key is not a problem - it must be true.

in *what* cases?

As stated, the table you are joining to *must* be able to have a primary/unique constraint (if it cannot - the update of the join is useless, but not any more useless than trying to use the table to update the other in the first place)


You can use merge if you didn't have the key in place, but the fact is - the KEY MUST BE "ok" to have in place in the first place.

create table big_table
as
select rownum id, a.*
from all_objects a
where 1=0
/
alter table big_table nologging;

declare
l_cnt number;
l_rows number := &1;
begin
insert /*+ append */
into big_table
select rownum, a.*
from all_objects a
where rownum <= &1;

l_cnt := sql%rowcount;

commit;

while (l_cnt < l_rows)
loop
insert /*+ APPEND */ into big_table
select rownum+l_cnt,
OWNER, OBJECT_NAME, SUBOBJECT_NAME,
OBJECT_ID, DATA_OBJECT_ID,
OBJECT_TYPE, CREATED, LAST_DDL_TIME,
TIMESTAMP, STATUS, TEMPORARY,
GENERATED, SECONDARY
from big_table
where rownum <= l_rows-l_cnt;
l_cnt := l_cnt + sql%rowcount;
commit;
end loop;
end;
/

alter table big_table add constraint
big_table_pk primary key(id)
/

begin
dbms_stats.gather_table_stats
( ownname => user,
tabname => 'BIG_TABLE',
method_opt => 'for all indexed columns',
cascade => TRUE );
end;
/
select count(*) from big_table;