Data Migration/Manuplation operations are common in projects, in this case we will use “Bulk Processing” for fastest UPDATE operations in Oracle Databases. We have two staging tables; xxln.xxln_bom_operational_routings xbor and xxln.xxln_bom_operation_sequences . We are moving old sequence values on our production tables in E-Business Suite BOM (Bill of materials) schema .

This PL/SQL code shows how it works ;

declare 

cursor c1 is 
      select /*+ FIRST_ROWS
             */ 
             bos.rowid as main_comp_rowid, 
             xbos.routing_sequence_id,
             xbos.operation_sequence_id
            from xxln.xxln_bom_operational_routings xbor, 
                 xxln.xxln_bom_operation_sequences xbos ,   
                 mtl_system_items_b msib, 
                 bom_operational_routings bor,
                 bom_operation_sequences bos ,
                 mtl_parameters mp  
            where xbor.assembly_item_code = msib.segment1
              and xbor.organization_code = mp.organization_code
              and bor.organization_id=mp.organization_id
              and msib.organization_id = mp.organization_id
              and bor.assembly_item_id = msib.inventory_item_id
              and bor.organization_id = mp.organization_id
              and bos.routing_sequence_id = bor.routing_sequence_id
              and xbos.routing_sequence_id= xbor.routing_Sequence_id
              and nvl(bor.alternate_routing_designator ,'-99') = nvl(xbor.alternate_routing_designator,'-99')
              and nvl(bos.disable_date,sysdate-1000) = nvl(xbos.disable_date,sysdate-1000)  
              and bos.operation_seq_num = xbos.operation_seq_num ;              
              
type crec_type is table of c1%rowtype index by binary_integer; 
crec crec_type ; 

begin  

        open c1 ; 
        loop 
        fetch c1 bulk collect into crec limit 2000000 ; 
            forall i in crec.first..crec.last 
                     update bom_operation_sequences set 
                            attribute14 = crec(i).routing_sequence_id,
                            attribute15 = crec(i).operation_sequence_id,
                            last_update_date=sysdate,
                            last_updated_by=-1
                            where rowid =crec(i).main_comp_rowid ; 
        commit; 
        exit when c1%notfound ; 
        end loop ; 
          
end ;

We use main tables rowid on UPDATE operation , FIRST_ROWS hint and combine it fetching data with BULK COLLECT INTO and FORALL LOOP for best result. You can also divide the query by adding a condition and execute it in different times , this helps cleaning rollback segments (caused by commit) if you are working on VLDB ‘s .

Thanks!