Extract data in parallel using rowid Logic

Using rowid logic to extract data in parallel when we migrate or synchronize data, we often encounter oracle needs to read rollback segments for tables with frequent application changes, it may lead to a very slow process. Sometimes it may even report the ora01555 error. For example, if a table is larger than 40 GB, It is the monthly partition data of a month, at this time, if you want to extract data to another database as soon as possible, there are several methods: Method 1: www.2cto.com everyone knows how to use append without writing logs, parallel extraction method: the code is similar to the following: [SQL] alter session enable parallel DML; alter session set db_file_multiblock_read_count = 128; INSERT/* + append parallel (B 2) */INTO OS _USER_SERVICE_HIS_1 B SELECT/* + FULL () PARALLEL (A, 2 )*/ * FROM OS _USER_SERVICE_HIS A WHERE CREATETIME> = TO_DATE ('000000', 'yyyymmdd'); Method 2: www.2cto.com open four sessions: the createtime logic is used to perform parallel processing of four time intervals. For example, if one month is used, it is divided into one session for the first week, and then until four sessions are processed for 4th weeks. Of course, you can also perform further partitioning: 1 session write cycle: one hour. Method 3: Use rowid for Parallelism: Here I will explain how to use rowid for Parallelism: real case: [SQL] create table ROWID_ OS _USER_BEHAVIOR_201212 (ID NUMBER, ROWID_MIN VARCHAR2 (32 ), ROWID_MAX VARCHAR2 (32), flag number); first, create a rowid to save the table: Get the data_object_id of the remote database: [SQL] 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 -------------- 2180 43 -- get the minimum, maximum rowid of the remote database: [SQL] SQL> insert into rowid_ OS _user_behavior_201212 (id, rowid_min, rowid_max, FLAG) 2 select rownum, 3 DBMS_ROWID.ROWID_CREATE@mail139.localdomain (, e. RELATIVE_FNO, e. BLOCK_ID, 0), 4 DBMS_ROWID.ROWID_CREATE@mail139.localdomain (, 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 Nd e. owner = 'oss01' 8 and partition_name = 'OS _ USER_BEHAVIOR_MONTH2012M12' 9; 659 rows inserted; commit; -- put the rowid of the extents range corresponding to the remote partition into the table: After insertion, the query results are as follows: SQL> select * from region where flag = 0 and rownum = 1; ID ROWID_MIN ROWID_MAX FLAG -------- region ---------- 422 AAA1O7AAxAADDgJAAA prepare 0 write the data storage process as follows: [s Ql] create or replace procedure transaction (I integer) is vSTATEDATE region; vUSERNUMBER region; vSERVICEID region; vOPERTYPE region; vRECVCOUNT region; vSENDCOUNT region; vTOTALCOUNT region; vPRESENDCOUNT region; region Region region; vENTERPRISES HEETNO response; vCREATETIME response; vMODIFYTIME response; vPROVCODE response; vSERVICEITEM response; vCARDTYPE response; vAREACODE response; vBINDTYPEID response; vORDERTYPE response; vMAILSERVICEITEM response;/* vCounter number: = 1; */vCounter_out number: = 0; c Ur_syncdata sys_refcursor; begin for x in (select * from rowid_ OS _USER_BEHAVIOR_201212 where mod (id, 4) = I --- you can change the stored procedure to a stored procedure by passing parameters, and flag = 0) loop begin open cur_syncdata for select/* + rowid (t) */STATEDATE, USERNUMBER, SERVICEID, OPERTYPE, RECVCOUNT, SENDCOUNT, TOTALCOUNT, PRESENDCOUNT, incluiseflag, ENTERPRISESHEETNO, CREATETIME, MODIFYTIME, PROVCODE, SERVICEITEM, CARDTYPE, AREACOD E, 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 into bulk collect into vSTATEDATE, vUSERNUMBER, vSERVICEID, vOPERTYPE, vRECVCOUNT, vSENDCOUNT, vTOTALCOUNT, vPRESENDCOUNT, interval, interval, vCREATETIME, vMODIFYTIME, interval E, vSERVICEITEM, vCARDTYPE, vAREACODE, vBINDTYPEID, vORDERTYPE, vMAILSERVICEITEM limit 5000; forall row in 1 .. vUSERNUMBER. count () insert into partition (STATEDATE, USERNUMBER, SERVICEID, OPERTYPE, RECVCOUNT, SENDCOUNT, TOTALCOUNT, PRESENDCOUNT, descriiseflag, hour, CREATETIME, MODIFYTIME, PROVCODE, SERVICEITEM, CARDTYPE, AREACODE, BINDTYPEID, ORDERTYPE, MAILSERV ICEITEM) 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 if; */exit when cur_syncdata % notfound; exception when others then dbms_output.put_line (sqlerrm); rollback; return; end loop; end; -- update Tag: update rowid_ OS _USER_BEHAVIOR_201212 set flag = 1 where id = x. id; commit; end loop; dbms_output.put_line ('total processing' | vCounter_out | 'records! '); End; then four sessions are opened, and data of, 2, and 3 can be: 30 GB are passed in. After testing, four parallel processes can be pulled in about 40 minutes, the application here is that dml data is often required for pulling data, with obvious advantages.