开4个会话:
通过createtime逻辑上进行4个时间区间的并行处理:比如一个月的话,分成1会话处理第一周,然后一直到4会话处理第4周
,当然你也可以再细分:1会话写循环一小时一小时处理。
方法3:
使用rowid并行:
这里我重要说下使用rowid并行的方法:
真实案例:
- create table ROWID_OS_USER_BEHAVIOR_201212
- (
- ID NUMBER,
- ROWID_MIN VARCHAR2(32),
- ROWID_MAX VARCHAR2(32),
- FLAG NUMBER
- );
首先创建rowid保存表:
获取远程库的data_object_id:
- SQL> select data_object_id from Dba_objects@mail139.localdomain where object_name='OS_USER_BEHAVIOR_MONTH' and subobject_name='OS_USER_BEHAVIOR_MONTH2012M12'
- 2 ;
- DATA_OBJECT_ID
- --------------
- 218043
--获取远程库的最小,最大rowid:
- SQL> insert into rowid_os_user_behavior_201212(id,rowid_min,rowid_max,FLAG)
- 2 select rownum,
- 3 DBMS_ROWID.ROWID_CREATE@mail139.localdomain(1,218043,e.RELATIVE_FNO,e.BLOCK_ID,0),
- 4 DBMS_ROWID.ROWID_CREATE@mail139.localdomain(1,218043,e.RELATIVE_FNO,e.BLOCK_ID+e.BLOCKS-1,10000),
- 5 0
- 6 from dba_extents@mail139.localdomain e where e.segment_name='OS_USER_BEHAVIOR_MONTH'
- 7 and e.owner='OSS01'
- 8 and partition_name='OS_USER_BEHAVIOR_MONTH2012M12'
- 9 ;
- 659 rows inserted;
- commit;
--将远程这个分区对应的extents范围的rowid放入表中:
插入完之后,查询结果如下:
SQL> select * from rowid_os_user_behavior_201212 where flag =0 and rownum =1;
ID ROWID_MIN ROWID_MAX FLAG
---------- -------------------------------- -------------------------------- ----------
422 AAA1O7AAxAADDgJAAA AAA1O7AAxAADFgICcQ 0
编写拉数据存储过程: 如下:
- create or replace procedure p_ods_os_user_beha_month(i integer) is
- vSTATEDATE dbms_sql.NUMBER_Table;
- vUSERNUMBER dbms_sql.VARCHAR2_Table;
- vSERVICEID dbms_sql.NUMBER_Table;
- vOPERTYPE dbms_sql.NUMBER_Table;
- vRECVCOUNT dbms_sql.NUMBER_Table;
- vSENDCOUNT dbms_sql.NUMBER_Table;
- vTOTALCOUNT dbms_sql.NUMBER_Table;
- vPRESENDCOUNT dbms_sql.NUMBER_Table;
- vENTERPRISEFLAG dbms_sql.NUMBER_Table;
- vENTERPRISESHEETNO dbms_sql.VARCHAR2_Table;
- vCREATETIME dbms_sql.DATE_Table;
- vMODIFYTIME dbms_sql.DATE_Table;
- vPROVCODE dbms_sql.NUMBER_Table;
- vSERVICEITEM dbms_sql.VARCHAR2_Table;
- vCARDTYPE dbms_sql.NUMBER_Table;
- vAREACODE dbms_sql.NUMBER_Table;
- vBINDTYPEID dbms_sql.NUMBER_Table;
- vORDERTYPE dbms_sql.NUMBER_Table;
- vMAILSERVICEITEM dbms_sql.VARCHAR2_Table;
- /* vCounter number := 1;*/
- vCounter_out number := 0;
- cur_syncdata sys_refcursor;
- begin
- for x in (select *
- from rowid_OS_USER_BEHAVIOR_201212
- where mod(id, 4) = i ---这里就是变量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 <a href="mailto:readonly.vw_os_user_behavior_mon1212@mail139.localdomain">readonly.vw_os_user_behavior_mon1212@mail139.localdomain</a> 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的数据,优势比较明显。