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.

• ypercubeᵀᴹ: Edit your question and add the CREATE TABLE statements, the indexes on the 2 tables and the execution plan of the Update statement.
• Harry: I edited the question. I hope all is clear.
• David Aldridge: How long do those operations currently take, and how long do you think they should take?

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;