Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
1  
Your update statement can't possibly be correct, ... WHERE AND ... will never work. –  Colin 't Hart Sep 24 '13 at 10:09
1  
What's the execution plan for the update? Are there any indexes on the 2 tables? –  Colin 't Hart Sep 24 '13 at 10:11
    
Edit your question and add the CREATE TABLE statements, the indexes on the 2 tables and the execution plan of the Update statement. –  ypercube Sep 24 '13 at 12:26
    
I edited the question. I hope all is clear. –  Harry Sep 24 '13 at 12:31
2  
How long do those operations currently take, and how long do you think they should take? –  David Aldridge Sep 25 '13 at 9:03
add comment

1 Answer

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;
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.