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?
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
TBL_REPORTING has indices:
an index on
but not on