oracle 触发器延时,数据库间用触发器同步数据，响应慢得要死。。。

如果是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;