Oracle JOB实现多线程插入

--参考之TOM 编程艺术中的DIY并行

--经测试，大数据量的插入，多线程在普通磁盘执行效率反而更慢，不如单insert语句，而在磁盘阵列硬件环境下执行效率有很大的提升。



--创建表,模拟多线程插入(TT3->TT4)
DROP TABLE TT3;
DROP TABLE TT4;
CREATE TABLE TT4 AS SELECT * FROM DBA_OBJECTS WHERE 1=0;
CREATE TABLE TT3 AS SELECT * FROM DBA_OBJECTS;
--数据分批插入参数表
DROP TABLE JOB_PARMS;
CREATE TABLE JOB_PARMS
 ( 
 JOB NUMBER PRIMARY KEY,
 LO_RID INT,
 HI_RID INT
 );
--创建插入的存储过程
CREATE OR REPLACE PROCEDURE PROC_TEST(P_JOB IN NUMBER) IS
 L_REC JOB_PARMS%ROWTYPE;
 BEGIN
 SELECT * INTO L_REC
 FROM JOB_PARMS
 WHERE JOB = P_JOB;
 INSERT INTO TT4
 SELECT A.OWNER,
       A.OBJECT_NAME,
       A.SUBOBJECT_NAME,
       A.OBJECT_ID,
       A.DATA_OBJECT_ID,
       A.OBJECT_TYPE,
       A.CREATED,
       A.LAST_DDL_TIME,
       A.TIMESTAMP,
       A.STATUS,
       A.TEMPORARY,
       A.GENERATED,
       A.SECONDARY
  FROM (SELECT ROWNUM RN, TT3.* FROM TT3 WHERE ROWNUM <= L_REC.HI_RID) A
 WHERE A.RN >= L_REC.LO_RID;
  DELETE FROM JOB_PARMS WHERE JOB = P_JOB;
   COMMIT;
   END;
/
---DIY 并行调度程序块
DECLARE
L_JOB NUMBER;
C_INDEX NUMBER;--插入的数量总数
S_INDEX INT:=0;--插入的开始index
E_INDEX INT:=0;--插入的结束index
CQ_INDEX INT:=20;--循环的次数
NUM_INCREASE INT:=0;--增量累加
V_I INT:=0;--计数器
BEGIN
SELECT COUNT(*) INTO C_INDEX FROM TT3;
NUM_INCREASE:= CEIL(C_INDEX/CQ_INDEX);
WHILE CQ_INDEX > V_I
LOOP
V_I:=V_I+1;
S_INDEX:=1+NUM_INCREASE*(V_I-1);
IF(V_I = 20) THEN--当等于循环次数则修改结束的index
E_INDEX:= C_INDEX;
ELSE 
E_INDEX:=NUM_INCREASE*V_I;
END IF;
DBMS_JOB.SUBMIT( L_JOB, 'PROC_TEST(JOB);');
INSERT INTO JOB_PARMS(JOB, LO_RID, HI_RID)
VALUES ( L_JOB, S_INDEX, E_INDEX );
END LOOP;
END;
/
--输入COMMIT开始执行
COMMIT;

--查看job是否执行完成

SELECT * FROM DBA_JOBS_RUNNING;