下面是我写的触发器,当数据导入到 USERINFO_TEMP之后执行 下面的触发器执行效率如何 当USERINFO数据达到上百万条数据的时候,执行效率有多高,如果不高的话 帮忙写个高效率的 在这里谢谢了
CREATE OR REPLACE
TRIGGER USERINFO_TEMP_INSERT_TG
AFTER INSERT ON USERINFO_TEMP
BEGIN
MERGE INTO userinfo u
USING userinfo_temp ut
ON (u.name = ut.name)
WHEN MATCHED THEN
UPDATE
SET u.sex = ut.sex, u.cred_name = ut.cred_name, u.cred_number = ut.cred_number,
u.MARITAL_STATUS = ut.MARITAL_STATUS,u.ANNUAL_INCOME=ut.ANNUAL_INCOME,u.CONTACT_STATUS = ut.CONTACT_STATUS
WHERE u.cred_number = ut.cred_number
when not matched then
insert (u.NAME, u.SEX, u.CRED_NAME, u.CRED_NUMBER, u.MARITAL_STATUS, u.ANNUAL_INCOME, u.CONTACT_STATUS)
values(ut.NAME, ut.SEX, ut.CRED_NAME, ut.CRED_NUMBER, ut.MARITAL_STATUS, ut.ANNUAL_INCOME, ut.CONTACT_STATUS)
where ut.name not in (select name FROM userinfo);
END;
CREATE OR REPLACE
TRIGGER USERINFO_TEMP_INSERT_TG
AFTER INSERT ON USERINFO_TEMP
BEGIN
MERGE INTO userinfo u
USING userinfo_temp ut
ON (u.name = ut.name)
WHEN MATCHED THEN
UPDATE
SET u.sex = ut.sex, u.cred_name = ut.cred_name, u.cred_number = ut.cred_number,
u.MARITAL_STATUS = ut.MARITAL_STATUS,u.ANNUAL_INCOME=ut.ANNUAL_INCOME,u.CONTACT_STATUS = ut.CONTACT_STATUS
WHERE u.cred_number = ut.cred_number
when not matched then
insert (u.NAME, u.SEX, u.CRED_NAME, u.CRED_NUMBER, u.MARITAL_STATUS, u.ANNUAL_INCOME, u.CONTACT_STATUS)
values(ut.NAME, ut.SEX, ut.CRED_NAME, ut.CRED_NUMBER, ut.MARITAL_STATUS, ut.ANNUAL_INCOME, ut.CONTACT_STATUS)
where ut.name not in (select name FROM userinfo);
END;
merge into就是专门用来支持大数据量的更新和插入!
-- 我会把所有的触发器去掉,用存储过程取代!
-- 以下是我的一个繁忙的业务的表,可以看到每秒钟有8条以上的记录插入,记得之前上面有两个触发器,
-- 后来把undo表空间都撑爆啦!......(之后我把这两个触发器去掉,用存储过程替换才解决了问题)music@SZTYORA> SELECT max(logid) max_logid,
2 min(logid) min_logid,
3 max(logid)-min(logid) as lines,
4 (max(logid)-min(logid))/1440/60 as insert_perSecond,
5 count(logid) as real_lines
6 FROM musicloguser_detail
7 WHERE timed >= trunc(sysdate-1)
8 AND timed < trunc(sysdate); MAX_LOGID MIN_LOGID LINES INSERT_PERSECOND REAL_LINES
---------- ---------- ---------- ---------------- ----------
99702547 99008085 694462 8.03775463 685420