--在测试环境上测试delete trigger对update语句效率的影响
1、找到一张大表
test_table 数据量 330万
2、update操作
declare
cursor cur_id is
select t.ID from test_table t where t.IDis not null
and rownum < 100000;
rec_id cur_id%rowtype;
begin
for rec_id in cur_id loop
update test_table set date_updated=sysdate where ID = rec_id.ID;
end loop;
end;
/
--7.547 seconds
rollback;
3、创建delete trigger
create table test_table_copy as select * from test_table where 1=2;
CREATE TRIGGER del_test_table --1
before delete ON test_table --2
FOR EACH ROW
BEGIN
INSERT INTO test_table_copy
(ID,
DATE_UPDATED) --3
VALUES
(:old.ID,
:old.DATE_UPDATED); --6
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
4、update操作:
--6.262 seconds
rollback;
5、将trigger disable
alter trigger del_test_table disable;
6、update操作:
--6.437 seconds
rollback;
7、再将trigger enable
alter trigger del_test_table enable;
8、update操作:
--6.609 seconds
rollback;
--结论:delete trigger对于表的update语句没有明显的影响。
1、找到一张大表
test_table 数据量 330万
2、update操作
declare
cursor cur_id is
select t.ID from test_table t where t.IDis not null
and rownum < 100000;
rec_id cur_id%rowtype;
begin
for rec_id in cur_id loop
update test_table set date_updated=sysdate where ID = rec_id.ID;
end loop;
end;
/
--7.547 seconds
rollback;
3、创建delete trigger
create table test_table_copy as select * from test_table where 1=2;
CREATE TRIGGER del_test_table --1
before delete ON test_table --2
FOR EACH ROW
BEGIN
INSERT INTO test_table_copy
(ID,
DATE_UPDATED) --3
VALUES
(:old.ID,
:old.DATE_UPDATED); --6
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
4、update操作:
--6.262 seconds
rollback;
5、将trigger disable
alter trigger del_test_table disable;
6、update操作:
--6.437 seconds
rollback;
7、再将trigger enable
alter trigger del_test_table enable;
8、update操作:
--6.609 seconds
rollback;
--结论:delete trigger对于表的update语句没有明显的影响。