oracle可以多線程插入嗎？

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;

/