title: Update statement is too slow with Correlated subquery
body: <p>I am using below update statement and it is way too slow(it takes around 10-12 seconds)</p>
  
  <pre><code>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;    
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Table temp_final contains around 8k records and table table_name contains around 2 lac records</p>
  
  <p>Please let me know how could i improve performance of this query? </p>
  
  <p>Regards</p>