title: How can i optimize this Update statement?
body: <p>I have the following update statement</p>
  
  <pre><code>UPDATE CONTRACTSF CF
  SET CF.PAYDATE = (SELECT MAX(FLHTEMP.PAYDATE)
                        FROM FLHTEMP
                        WHERE
                        FLHTEMP.FLHDATE = CF.FLHDATE
                        AND FLHTEMP.FLHCOD = CF.FLHCOD
                        AND FLHTEMP.FLHUN = CF.FLHUN
                        AND FLHTEMP.FLHCONTRACT = CF.FLHCONTRACT)
  WHERE CF.FLHUN || CF.FLHCONTRACT || CF.FLHDATE 
          IN (SELECT DISTINCT FT.FLHUN || FT.FLHCONTRACT || FT.FLHDATE 
              FROM FLHTEMP FT)
  </code></pre>
  
  <p>When the FLHTEMP table has a lot of records (6M records), it takes many hours to complete.</p>
  
  <p>I have tried using IN with multiple columns instead of concatenating it with || but got no difference.</p>
  
  <p>I created a index of all the columns being used (FLHUN, FLHCONTRACT, FLHCOD, FLHDATE) but Plan explain shows me its not being used.</p>
  
  <p>The costly operations showed on Plan explain are a Hash Join, Sort Unique and Table full access</p>