Oracle JOB實現多執行緒插入

Sql程式碼

--經測試，大資料量的插入，多執行緒在普通磁碟執行效率反而更慢，不如單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;

/