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!