通过rowid逻辑并行抽取数据(二)
vCounter_out number := 0;
cur_syncdata sys_refcursor;
begin
for x in (select *
from rowid_OS_USER_BEHAVIOR_201212
where mod(id, 4) = i ---存储过程这里改下，其实也可以通过传参，变成一个存储过程，
and flag = 0) loop
begin
open cur_syncdata for
select /*+rowid(t))*/
STATEDATE,
USERNUMBER,
SERVICEID,
OPERTYPE,
RECVCOUNT,
SENDCOUNT,
TOTALCOUNT,
PRESENDCOUNT,
ENTERPRISEFLAG,
ENTERPRISESHEETNO,
CREATETIME,
MODIFYTIME,
PROVCODE,
SERVICEITEM,
CARDTYPE,
AREACODE,
BINDTYPEID,
ORDERTYPE,
MAILSERVICEITEM
from yefz.vw_os_user_behavior_mon1212@mail139.localdomain t
where rowid >= chartorowid(x.rowid_min)
and rowid <= chartorowid(x.rowid_max);
loop
begin
fetch cur_syncdata bulk collect
into vSTATEDATE, vUSERNUMBER, vSERVICEID, vOPERTYPE, vRECVCOUNT, vSENDCOUNT, vTOTALCOUNT, vPRESENDCOUNT, vENTERPRISEFLAG, vENTERPRISESHEETNO, vCREATETIME, vMODIFYTIME, vPROVCODE, vSERVICEITEM, vCARDTYPE, vAREACODE, vBINDTYPEID, vORDERTYPE, vMAILSERVICEITEM limit 5000;
forall row in 1 .. vUSERNUMBER.count()
insert into OS_USER_BEHAVIOR_MONTH_201212
(STATEDATE,
USERNUMBER,
SERVICEID,
OPERTYPE,
RECVCOUNT,
SENDCOUNT,
TOTALCOUNT,
PRESENDCOUNT,
ENTERPRISEFLAG,
ENTERPRISESHEETNO,
CREATETIME,
MODIFYTIME,
PROVCODE,
SERVICEITEM,
CARDTYPE,
AREACODE,
BINDTYPEID,
ORDERTYPE,
MAILSERVICEITEM)
values
(vSTATEDATE(row),
vUSERNUMBER(row),
vSERVICEID(row),
vOPERTYPE(row),
vRECVCOUNT(row),
vSENDCOUNT(row),
vTOTALCOUNT(row),
vPRESENDCOUNT(row),
vENTERPRISEFLAG(row),
vENTERPRISESHEETNO(row),
vCREATETIME(row),
vMODIFYTIME(row),
vPROVCODE(row),
vSERVICEITEM(row),
vCARDTYPE(row),
vAREACODE(row),
vBINDTYPEID(row),
vORDERTYPE(row),
vMAILSERVICEITEM(row));
vCounter_out := vCounter_out + sql%rowcount;
commit;
/* if vCounter = 1000 then
begin
dbms_lock.sleep(3);
vCounter := 0;
end;
end if;*/
exit when cur_syncdata%notfound;
exception
when others then
dbms_output.put_line(sqlerrm);
rollback;
return;
end;
end loop;
end;
--更新处理的标记位：
update rowid_OS_USER_BEHAVIOR_201212 set flag = 1 where id = x.id;
commit;
end loop;
dbms_output.put_line('共处理' || vCounter_out || '条记录!');
end;
然后开4个会话，分别传入0,1,2,3即可:
30G的数据，经过测试并行4个进程，大概40分钟可以拉完，这里的应用在于拉的数据是经常需要dml的数据，优势比较明显。