title: Oracle Update statement hanging
body: <p>I have an Oracle Update statement that is hanging when previous similar updates in the same script are finishing in about an hour.</p>
  
  <p>I have two tables: </p>
  
  <p>Table A: ID, Masked_Account, Original_Account (9+ million rows, a cross-reference table).</p>
  
  <p>Table B: UID, Prefix_Pan, Pan, Masked (1.3+ billion rows).</p>
  
  <p>I'm trying to update the Table B Pan column with the Table A Masked_Account column in each instance where the Table B Pan column matches aTable A Original_Account column.</p>
  
  <p>I am doing the Update in batches of 100 million records per batch with a subsequent Commit.  </p>
  
  <p>For example, the below listed Update SQL script finishes in about an hour:</p>
  
  <pre><code>UPDATE [TABLE B] optim
  SET optim.PAN = (SELECT MASKED_ACCOUNT FROM [TABLE A] pans
                   WHERE optim.PAN = pans.ORIG_ACCOUNT), optim.MASKED = '1'
  WHERE optim.OPTIM_UID BETWEEN 1200000000 AND 1300000000
  AND EXISTS (SELECT 1
              FROM [TABLE A] pans
              WHERE optim.PAN = pans.ORIG_ACCOUNT);
  </code></pre>
  
  <p>My last Update SQL statement hangs and never finishes:</p>
  
  <pre><code>UPDATE [TABLE B] optim
  SET optim.PAN = (SELECT MASKED_ACCOUNT FROM [TABLE A] pans
                   WHERE optim.PAN = pans.ORIG_ACCOUNT), optim.MASKED = '1'
  WHERE optim.OPTIM_UID &gt; 1300000000
  AND EXISTS (SELECT 1
              FROM [TABLE A] pans
              WHERE optim.PAN = pans.ORIG_ACCOUNT);
  </code></pre>
  
  <p>Explain Plan (dbms_xplan.display) results:</p>
  
  <pre><code>Plan hash value: 4037184420
  
  ----------------------------------------------------------------------------------------------------------------------
  | Id  | Operation                             | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |
  ----------------------------------------------------------------------------------------------------------------------
  |   0 | UPDATE STATEMENT                      |                              |   250 |  9250 |   465K  (1)| 00:00:10 |
  |   1 |  UPDATE                               | OPTIM_SMRY_FTR_CHKMC_EXTRACT |       |       |            |          |
  |*  2 |   HASH JOIN RIGHT SEMI                |                              |   250 |  9250 |   239K  (1)| 00:00:05 |
  |   3 |    INDEX STORAGE FAST FULL SCAN       | IDX_PANS_ORIG_ACCT           |    82 |   902 | 10123   (1)| 00:00:01 |
  |   4 |    TABLE ACCESS BY INDEX ROWID BATCHED| OPTIM_SMRY_FTR_CHKMC_EXTRACT |    32M|   796M|   228K  (1)| 00:00:05 |
  |*  5 |     INDEX RANGE SCAN                  | PK_OP_47                     |    22M|       | 53465   (1)| 00:00:02 |
  |   6 |   TABLE ACCESS BY INDEX ROWID BATCHED | PANS                         |     1 |    22 |   604   (1)| 00:00:01 |
  |*  7 |    INDEX RANGE SCAN                   | IDX_PANS_ORIG_ACCT           |     1 |       |     3   (0)| 00:00:01 |
  ----------------------------------------------------------------------------------------------------------------------
  
  Predicate Information (identified by operation id):
  ---------------------------------------------------
  
     2 - access("OPTIM"."PAN"="PANS"."ORIG_ACCOUNT")
     5 - access("OPTIM"."OPTIM_UID"&gt;1300000000)
     7 - access("PANS"."ORIG_ACCOUNT"=:B1)
  
  Note
  -----
     - dynamic statistics used: dynamic sampling (level=AUTO)
  </code></pre>
  
  <p>Can anyone tell me what I need to do differently on the last Update SQL (optim.OPTIM_UID > 1300000000) so that it won't hang or how I should approach the entire Update process differently?</p>