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;