title: Update query taking long time in oracle 10g
body: <p>I have a table which holds more then 2 million records, I am trying to update a table using following query </p>
  
  <pre><code>UPDATE toc T
  SET RANK =
          65535
        - (SELECT COUNT (*)
             FROM toc T2
            WHERE     S_KEY LIKE '00010001%'
                  AND A_ID IS NOT NULL
                  AND T2.TARGET = T.TARGET
                  AND T2.RANK &gt; T.RANK)
  WHERE S_KEY LIKE '00010001%' AND A_ID IS NOT NULL
  </code></pre>
  
  <p>Usually this query tooks 5 mins to update 50000 rows in our staging db which is a exact replica of production db but in our production db it is taking 6 hours to execute...</p>
  
  <p>I tried Oracle advisory to select the correct execution plan but nothing is working... </p>
  
  <p>Plan</p>
  
  <pre><code>UPDATE STATEMENT  ALL_ROWSCost: 329,471                 
      6 UPDATE TT.TOC             
          2 TABLE ACCESS BY INDEX ROWID TABLE TT.TOC Cost: 5  Bytes: 4,173,236  Cardinality: 54,911       
              1 INDEX SKIP SCAN INDEX TT.DATASTAT_SORTKEY_IDX Cost: 4  Cardinality: 1     
          5 SORT AGGREGATE  Bytes: 76  Cardinality: 1         
              4 TABLE ACCESS BY INDEX ROWID TABLE TT.TOC Cost: 5  Bytes: 76  Cardinality: 1   
                  3 INDEX SKIP SCAN INDEX TT.DATASTAT_SORTKEY_IDX Cost: 4  Cardinality: 1  
  </code></pre>
  
  <p>I can see the following wait events</p>
  
  <pre><code>  1,066 db file sequential read 10,267  0   3,993   0   6   39,933,580
  
    1,066 db file scattered read  413 0   188 0   6   1,876,464
  </code></pre>
  
  <p>Any help will be greatly appreciated.</p>
  
  <hr>
  
  <p>here is the current list of indexes</p>
  
  <pre><code> DSTAT_SKEY_IDX         D_STATUS        1
   DSTAT_SKEY_IDX         S_KEY           2
   IDX$$_165A0002         N_LABEL         1
   S_KEY_IDX              S_KEY               1
   XAK1_TOC               N_RELATIONSHIP      1
   XAK2_TOC               TARGET              1
   XAK2_TOC               N_LABEL             2
   XAK2_TOC               D_STATUS            3
   XAK2_TOC               A_ID                4
   XIE1_TOC               N_RELBASE           1
   XIF4_TOC               SOURCE_FILE_ID      1
   XIF5_TOC               A_ID                1
   XPK_TOC                N_ID                1
  </code></pre>
  
  <p>Atif</p>