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

妖狐艹你老母 2023-01-18 04:28 69阅读 0赞

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

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;

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

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 SqlServer到oracle同步数据update触发器

相关 SqlServer 同步到oracle 数据库触发器insert

相关 MySQL、PostgreSQL、Oracle、SQL Server数据库触发器实现同步数据

相关 oracle物化视图建立触发器,物化视图上用触发器（用于同步）

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

相关 ORACLE数据库触发器

相关 ORACLE触发器　行级触发器

相关 Oracle触发器

相关 oracle触发器~

相关 Oracle触发器