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;