Update statement is too slow with Correlated subquery

Question

I am using below update statement and it is way too slow(it takes around 10-12 seconds)

UPDATE temp_final t
   SET name = (
        SELECT DISTINCT(nm.name)
          FROM table_name nm,
               temp_final fn
         WHERE nm.id       = fn.senid
           AND fn.senid    = t.senid
           AND fn.rowgroup = t.rowgroup
           AND fn.mid      = t.mid
           AND fn.sid      = t.sid
           AND fn.pid      = t.pid
           AND fn.reid     = t.reid
           AND fn.retype   = t.retype
        )
 WHERE t.reid   = n_reid
   AND t.retype = n_retype
   AND t.sid    = c_sid
   AND t.pid    = n_pid;    

Columns of temp_final table are not indexed , as this table includes lot of data update. I think , I cannot convert correlated subquery to join as it is using distinct clause.Plan of this query gives below result:

OPERATION                              OBJECT        CARDINALITY     COST
- UPDATE STATEMENT                                       1925        337046      
  - UPDATE                             temp_final 
    -TABLE ACCESS(FULL)                temp_final        1925        171
      - Filter Predicates
        - AND
          -T.SID='123'
          -T.PID=21
          -T.REID=9
          -T.RETYPE=1
    -SORT(UNIQUE)                                         1          175
      - NESTED LOOPS                                      1          174 
         -TABLE ACCESS(BY INDEX ROWID)  TABLE_NAME        1           2
           -INDEX(UNIQUE SCAN)          NAME_PK           1           1  
             -ACCESS PREDICATES
               NM.ID=:B1
         -TABLE ACCESS(FULL)            temp_final        1           172
           - Filter Predicates
             - AND
               -fn.mid=:B1
               -fn.senid=:B2
               -fn.sid =:B3
               -fn.rowgroup =:B4
               -fn.pid=:B5
               -fn.reid=:B6
               -fn.retype=:B7
               -nm.id = fn.senid

Table temp_final contains around 8k records and table table_name contains around 2 lac records

Please let me know how could i improve performance of this query?

Regards

Answer 1

I don't fully understand your self join. Isn't this statement sufficient:

UPDATE temp_final t
   SET name = (
        SELECT nm.name
          FROM table_name nm
         WHERE nm.id       = t.senid
        )
 WHERE t.reid   = n_reid
   AND t.retype = n_retype
   AND t.sid    = c_sid
   AND t.pid    = n_pid;