Oracle 11g update of rows in a table with millions of records
<p>I am working on a table which has many fields and 100 million data and has a composite key over some fields. Sample table <strong>emp</strong> with few rows out of millions is shown below:</p>

<pre><code>+------+------+------+------+
| Col1 | Col2 | Col3 | Col4 |
+------+------+------+------+
|   11 |   21 |   31 | X    |
|   12 |   22 |   32 | X    |
|   14 |   24 |   34 | X    |
|   11 |   21 |   31 | 555  |
|   11 |   21 |   31 | 551  |
|   12 |   22 |   32 | 89   |
|   14 |   24 |   34 | 45   |
+------+------+------+------+
</code></pre>

<p>Suppose the composite is key is over <code>Col1</code> to <code>Col4</code>. Imagine Col1, Col2, Col3 together represent a group Id. First row has <code>Col4</code> as <code>X</code> and so I need to select all records from table whose <code>Col1 = 11</code> and <code>Col2 = 21</code> and <code>Col3 = 31</code>. Next since second row is 12,22,32,'X', I need select all rows with <code>Col1 = 11</code> and <code>Col2 = 22</code> and <code>Col3=32</code>. And so on for all cases where <code>Col4 =X</code>.</p>

<p>I tried using for loop with cursor but it is taking lot of time since the table has 100 million data and composite key. It is taking very very long time.</p>

<p>Pseudo code of loop statement i tried:</p>

<pre><code>result= select Col1,Col2,Col3 from emp where `Col4=X`.
for each row in result
do
   finalresult += select * from table where Col1 = row.Col1 and Col2 = row.Col2 and Col3 = row.Col3
done
</code></pre>

<p><strong>finalresult</strong> to have all the required data. But this is running for a very long time.</p>

<p>Also I tried merge to update as below</p>

<pre><code>    merge into emp t1
    using (select * from emp t where t.col4=X) t2
    on t1.col1=t2.col1
    and t1.col2=t2.col2
    and t1.col3=t3.col3
    when matched then update set col4=Y
and t1.col4!='X';
</code></pre>

<p>But this is not updating any rows.</p>

<p>I would really appreciate some help with this problem.</p>