如果是PLSQL/SQL ENGINE 切换的问题,可以考虑 COMPOUND TRIGGER用FORALL批量插入,下面代码供参考(需修改),可以先用一个表测试一下
CREATE OR REPLACE TRIGGER XI.TRG_INSERT_ORGN
FOR UPDATE ON XI.ORGN
COMPOUND TRIGGER
--You can adjust the thresh hold value below until reaching the optimal speed
threshhold CONSTANT SIMPLE_INTEGER := 1000;
TYPE cst_aat IS TABLE OF cst@XJ_2_XIJ%ROWTYPE INDEX BY SIMPLE_INTEGER;
csts cst_aat;
TYPE org_aat IS TABLE OF org@XJ_2_XIJ%ROWTYPE INDEX BY SIMPLE_INTEGER;
orgs org_aat;
... --define associative arrays for all remote tables you gonna perform DML on
idx_csts SIMPLE_INTEGER := 0;
idx_orgs SIMPLE_INTEGER := 0;
... --define idxs variables for all remote tables
PROCEDURE flush_array_cst IS
n1 CONSTANT SIMPLE_INTEGER := csts.count();
BEGIN
FORALL j IN 1..n1
INSERT INTO cst@XJ_2_XIJ
VALUES csts(j);
csts.DELETE();
idx_csts := 0;
END flush_array_cst;
PROCEDURE flush_array_org IS
n2 CONSTANT SIMPLE_INTEGER := orgs.count();
BEGIN
FORALL j IN 1..n2
INSERT INTO org@XJ_2_XIJ
VALUES orgs(j);
orgs.DELETE();
idx_orgs := 0;
END flush_array_org;
...--define procedures for all remote tables
BEFORE EACH ROW IS
BEGIN
IF LENGTH(:NEW.orgn_id)=7
THEN
idx_csts := idx_csts + 1;
--chagne below 'colx' to the actaul column names of the remote site tables
csts(idx).col1 := :new.orgn_no;
csts(idx).col2 := substr(:NEW.orgn_id,1,5);
csts(idx).col3 := :NEW.orgn_id;
csts(idx).col4 := :NEW.dpt_nm;
csts(idx).col5 := NULL;
csts(idx).col6 := '1';
csts(idx).col7 := '01';
csts(idx).col8 := 'A';
csts(idx).col9 := :NEW.dpt_nm;
csts(idx).col10 :=NULL;
csts(idx).col11 :=NULL;
csts(idx).col12 :='sys';
csts(idx).col13 :=SYSDATE;
csts(idx).col14 :='sys';
csts(idx).col15 :=SYSDATE;
ELSIF LENGTH(:NEW.orgn_id)=5
THEN
IF substr(:NEW.orgn_id,1,3)='001'
THEN
idx_orgs := idx_orgs + 1;
--chagne below 'colx' to the actaul column names of the remote site tables
orgs(idx).col1 := :NEW.orgn_no;
orgs(idx).col2 := :NEW.orgn_no;
orgs(idx).col3 := '00';
orgs(idx).col4 := :NEW.orgn_id;
orgs(idx).col5 := :NEW.dpt_nm;
orgs(idx).col6 := :NEW.dpt_nm;
orgs(idx).col7 := NULL;
orgs(idx).col8 := 'A';
orgs(idx).col9 := '20';
...
ELSE
...--other rules here
END IF;
END IF;
IF idx_csts >= threshhold THEN
flush_array_cst();
END IF;
IF idx_orgs >= threshhold THEN
flush_array_org();
END IF;
...--other threshhold checks here
END BEFORE EACH ROW;
-- AFTER STATEMENT Section:
AFTER STATEMENT IS
BEGIN
flush_array_cst();
flush_array_org();
...--flush all other arrays into table
END AFTER STATEMENT;
END;