|
|
|
Re: Performance issue with Update statement having select Statement [message #686732 is a reply to message #686731] |
Mon, 05 December 2022 06:37 |
Solomon Yakobson
Messages: 3286 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Your query updates all rows in DOC_MIGRATION regardless if there is a match or not. So unless there is FK on rdm.oc_id referencing dlo.i_chronicle_id it should be:
UPDATE /*+ PARALLEL(8)*/ DOC_MIGRATION RDM
SET RDM.LATEST_VERSION_DOCID = (
SELECT DLO.R_OBJECT_ID
FROM DOC_LATEST_OBJECTID DLO
WHERE RDM.OC_ID = DLO.I_CHRONICLE_ID
)
WHERE RDM.OC_ID IN (
SELECT DLO.I_CHRONICLE_ID
FROM DOC_LATEST_OBJECTID DLO
)
/
Or better use MERGE:
MERGE
INTO DOC_MIGRATION RDM
USING DOC_LATEST_OBJECTID DLO
ON (
RDM.OC_ID = DLO.I_CHRONICLE_ID
)
WHEN MATCHED
THEN
UPDATE
SET RDM.LATEST_VERSION_DOCID = DLO.R_OBJECT_ID
/
SY.
|
|
|
|