Vast data replacement between two tables in Oracle
<p>Assume that we have two tables, named Tb1 and Tb2 and we are going to replace data from one to another. Tb1 is the main source of data and Tb2 is the Destination. This replacement operation has 3 parts. </p>

<p>In the first part we are going to validate all rows in Tb1 and check if they are correct. For example National security code must exactly have 10 digits or a real customer must have a valid birth date so according to these validation rules, 28 different validation methods and error codes have been considered. <strong>During the validation every spoiled row's description and status will be updated to a new state.</strong> </p>

<p>Part 2 fixes the rows' problems and the third one replace them to the Tb2.</p>

<p>For instance this row says that it has 4 different error. </p>

<blockquote>
  <p>-- Tb1.desc=6,8,14,16<br>
  -- Tb1.sts=0</p>
</blockquote>

<p>A correct row of data</p>

<blockquote>
  <p>-- Tb1.desc=Null  i
  -- Tb1.sts=1</p>
</blockquote>

<p>I have been working on the first part recently and have come up with a solution which works fine but it is too slow. Unfortunately It takes exactly 31 minutes to validate 100,000 rows. In a real situation we are going to validate <strong>more than 2 million records</strong> so it is totally useless despite all it's functionality.</p>

<p>Let's take look at my package : </p>

<pre><code>procedure Val_primary IS
      begin    
        Open X_CUSTOMER;
              Loop            
                fetch X_CUSTOMER bulk collect into CUSTOMER_RECORD;
                EXIT WHEN X_CUSTOMER%notfound;                  
                For i in CUSTOMER_RECORD.first..CUSTOMER_RECORD.last loop       
                Val_CTYP(CUSTOMER_RECORD(i).XCUSTYP);
                Val_BRNCH(CUSTOMER_RECORD(i).XBRNCH);

                --Rest of the validations ...

                UptDate_Val(CUSTOMER_RECORD(i).Xrownum);
                end loop;
                CUSTOMER_RECORD.delete;                              
              End loop;
         Close X_CUSTOMER;         
      end Val_primary;
</code></pre>

<p>Inside a validation procedure : </p>

<pre><code>procedure Val_CTYP(customer_type IN number)IS
  Begin
    IF(customer_type&lt;1 or customer_type&gt;3)then
      RW_FINAL_STATUS:=0;
      FINAL_ERR_DSC:=Concat(FINAL_ERR_DSC,ERR_INVALID_CTYP);
    End If;
  End Val_CTYP;
</code></pre>

<p>Inside the update procedure : </p>

<pre><code>procedure UptDate_Val(rownumb IN number) IS 
  begin 
    update tb1 set tb1.xstst=RW_FINAL_STATUS,tb1.xdesc=FINAL_ERR_DSC where xc1customer.xrownum=rownumb; 
    RW_FINAL_STATUS:=1;
    FINAL_ERR_DSC:=null;
  end UptDate_Val;
</code></pre>

<p><strong>Is there any way to reduce execution time ?</strong> 
It must be done less than 20 minutes for more than 2 million records.</p>