Oracle千万级数据处理

--插入时不产生日志， --alter table cux_mtl_material_vmi2024038 nologging; --记录时间 --set timing on; declare cursor cur is select mmt.organization_id, mmt.subinventory_code, mmt.transaction_quantity, mmt.transfer_organization_id, mmt.transfer_subinventory, mmt.owning_organization_id, mmt.xfr_owning_organization_id, mmt.inventory_item_id, mmt.transaction_id, mmt.transaction_date, sysdate -- mmt.creation_date from mtl_material_transactions mmt where mmt.transaction_type_id = 74 and mmt.owning_organization_id in (select org.organization_id from org_organization_definitions org); type rec is table of cux_mtl_material_vmi2024038%rowtype; recs rec; begin open cur; while (true) loop --批量提交控制(每100w提交一次) fetch cur bulk collect into recs limit 1000000; --捆绑插入(减少与服务器交互的次数) forall i in 1 .. recs.count insert /+append(6)/ into cux_mtl_material_vmi2024038 values recs(i); commit; exit when cur%notfound; end loop; close cur; end;