Re: ERROR ORA-01779:cannot modify a column which maps to a non-key-preserved

Alexandra wrote:
> Hi together,
>
> I have a problem executing the following query:
>
> UPDATE
> ( SELECT PERSON_SSKEY , PERSONEN_ID_KOPF
> FROM DIM_PERSON INNER JOIN DUBLETTE_REFERENZ
> ON PERSON_SSKEY = PERSONEN_ID_FOLGE)
> SET PERSON_SSKEY = PERSONEN_ID_KOPF
>
> Primary key table DIM_PERSON is PERSON_SSKEY
> Primary key table DUBLETTE_REFERENZ is PERSONEN_ID_FOLGE
>
>
> There can be multiple rows for PERSONEN_ID_KOPF which, in my opinion
> causes the problem.
>
> Can somebody help me with that?
>
> What can I do to make the query run?
>
> Earlier I used tho following query:
>
> UPDATE DIM_PERSON p
> SET p.PERSON_SSKEY =
> (SELECT dr.PERSONEN_ID_KOPF
> FROM DUBLETTE_REFERENZ dr
> WHERE p.PERSON_SSKEY = dr.PERSONEN_ID_FOLGE)
> WHERE p.PERSON_SSKEY
> IN ( SELECT PERSONEN_ID_FOLGE
> FROM DUBLETTE_REFERENZ)
>
> But as there are about 12 million rown in DIM_PERSON the runtime is too
> long (no result after 7 hours).
>
> That's why I've tried to optimize it, which is unfortunatelly not
> working :-(((
>
> Would be so glad if somebody has an idea!!!!
>
> Thanks in advance,
> Alexandra

1 Did you try to understand the error message? It tells you are updating the join column in an inline view. Obviously this isn't possible.
2 Did you run explain plan on the original statement? What did it show? 3 Is personen_id_folge
a) indexed
b) NULL allowd
The latter may force a full table scan in your IN subquery, if you are using CBO
Either: make the column mandatory.
or
convert the subquery in a where exists correlated subquery.

Hth

-- 
Sybrand Bakker
Senior Oracle DBA