Oracle 10g: Optimizing an update query that uses a subquery

Question

I have the following query within a stored procedure, and I've been trying to improve its performance to no avail.

  UPDATE TBL_REPORTING REP
  SET    (REP.ISSUER_ID, REP.ISSUER_NAME
         ) = ( SELECT DISTINCT TMP.ISSUER_ID
                       ,TMP.ISSUER_NAME
                FROM   TMP_REPORTING TMP
                WHERE  TMP.CMD_KEY = REP.CMD_KEY
                AND    TMP.REPORTING_KEY = REP.REPORTING_KEY
            )
  WHERE  REP.CMD_KEY = v_cmd_key;

TBL_REPORTING has 332124 rows in total. TMP_REPORTING has 55589 rows in total.

I have tried rewriting the subquery using the WITH clause, but I can't get it working when there's 2 columns in the SET statement. I was reading that perhaps I could use the MERGE statement, but I haven't been able to get a working query yet. Ideas?

Update:

I managed to rewrite the query using the MERGE statement, but I still need to check if the results are the same:

MERGE INTO TBL_REPORTING REP
USING (SELECT DISTINCT TMP.CMD_KEY, TMP.REPORTING_KEY, TMP.ISSUER_ID, TMP.ISSUER_NAME
            FROM TMP_REPORTING TMP
            WHERE TMP.CMD_KEY = v_cmd_key) comp
            ON (REP.CMD_KEY = comp.CMD_KEY AND REP.REPORTING_KEY = comp.REPORTING_KEY)
            WHEN MATCHED THEN UPDATE SET ISSUER_ID = comp.ISSUER_ID, ISSUER_NAME = comp.ISSUER_NAME;

Update 2: Results are not the same. The UPDATE statement updates 40 more rows than the MERGE statement.

Execution plan for update:

Plan
UPDATE STATEMENT  ALL_ROWSCost: 644,432  Bytes: 322,065  Cardinality: 7,157                 
    6 UPDATE TBL_REPORTING          
        2 TABLE ACCESS BY INDEX ROWID TABLE TBL_REPORTING Cost: 302  Bytes: 322,065  Cardinality: 7,157         
            1 INDEX RANGE SCAN INDEX IDX_TBL_REPORTING_R4 Cost: 8  Cardinality: 7,157   
        5 SORT UNIQUE  Cost: 44  Bytes: 45  Cardinality: 1          
            4 TABLE ACCESS BY INDEX ROWID TABLE TMP_REPORTING Cost: 43  Bytes: 45  Cardinality: 1   
                3 INDEX RANGE SCAN INDEX TMP_REPORTING_13R2 Cost: 10  Cardinality: 6,949  

Table TBL_REPORTING has indices:
PK_TBL_REPORTING on REPORTING_KEY and
IDX_TBL_REPORTING_R4 on CMD_KEY.

Table TMP_REPORTING has:
an index on CMD_KEY,
but not on REPORTING_KEY.

Answer 1

Try the below to update unique issuer id and issuer name instead of using Distinct by using Group by together with the index on Issue Id and Issuer Name in Temporary reporting table

Kindly refer the below link Fast Query To Get TOP-N Distinct Rows From A Huge Data Table

UPDATE TBL_REPORTING REP
SET(REP.ISSUER_ID, REP.ISSUER_NAME
) = ( SELECT TMP.ISSUER_ID
                   ,TMP.ISSUER_NAME
            FROM   TMP_REPORTING TMP
            WHERE  TMP.CMD_KEY = REP.CMD_KEY
            AND    TMP.REPORTING_KEY = REP.REPORTING_KEY
            GROUP BY TMP.ISSUER_ID
                   ,TMP.ISSUER_NAME
        )
WHERE  REP.CMD_KEY = v_cmd_key;