Home » SQL & PL/SQL » SQL & PL/SQL » Performance issue with Update statement having select Statement
Performance issue with Update statement having select Statement [message #686718] Thu, 01 December 2022 06:21 Go to next message
VVR
Messages: 2
Registered: December 2022
Junior Member
Hi All,

I'd like to request assistance with the update statement, which would have the select statement take high performance issue for 94 lakh records.

UPDATE /*+ PARALLEL(Cool */ 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)

Much Appreciated in advance.

Regards,
Vikas

Re: Performance issue with Update statement having select Statement [message #686719 is a reply to message #686718] Thu, 01 December 2022 08:07 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

For any performances question, please read http://www.orafaq.com/forum/index.php/mv/msg/206002/433888/#msg_433888 and post the required information.

Re: Performance issue with Update statement having select Statement [message #686731 is a reply to message #686719] Mon, 05 December 2022 03:36 Go to previous messageGo to next message
VVR
Messages: 2
Registered: December 2022
Junior Member
Hi,
I have formatted the sql and also the oracle version is 19c(19.0.0.0.0)

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)

Regards,
VVR
Re: Performance issue with Update statement having select Statement [message #686732 is a reply to message #686731] Mon, 05 December 2022 06:37 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
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.
Re: Performance issue with Update statement having select Statement [message #686733 is a reply to message #686731] Tue, 06 December 2022 01:00 Go to previous message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
UPDATE /*+ PARALLEL(8)*/ DOC_MIGRATION rdm
Have you enabled parallel DML for your session? Just using that hint is not enough.
Previous Topic: Function not working
Next Topic: Value multiple
Goto Forum:
  


Current Time: Thu Mar 28 14:01:13 CDT 2024