Oracle Trigger 性能测试

前一段对用户表做了个触发器:TRI_USER,当表有更新操作时触发,触发字段:UPDATE_TIME,记录记录修改日期时间.

USER表结构

SQL > DESC USER(

user_id number(20) not null primary key,

user_name varchar(128) not null,

user_password varchar(128) not null,

user_mailbox varchar(128),

user_flag number(2),

user_zip number(10),

user_address varchar(255),

user_address2 varchar(255),

user_mobilephone number(20),

regist_time date,

update_tim date)

当前数据量三千万条,使用存储过程分三次插入/更新用户表,日志记录过程执行时间.操作数据10万条.

1.插入过程

declare
v_i number;
v_time varchar2(10);

begin
v_i:=0;

select to_char(sysdate,'sssss')
into v_time
from dual;
dbms_output.put_line(v_time);

for v_i in 1..100000 loop
insert into user (user_id,user_name,user_password,user_mailbox,user_flag,user_zip,user_address,user_address2,user_mobilephone,regist_time)
values(v_i,'testtrigger','123456','test@test.com',1,'100000','address1','address2','13000000000',to_date('2006-06-06 10:10:10','yyyy-mm-dd hh24:mi:ss'));
commit;
end loop;

select to_char(sysdate,'sssss')
into v_time
from dual;
dbms_output.put_line(v_time);
end;
/
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

2.更新过程
declare
v_i number;
v_time varchar2(10);

begin
v_i:=0;
select to_char(sysdate,'sssss')
into v_time
from dual;
dbms_output.put_line(v_time);
dbms_output.put_line(v_xing);
for v_i in 1..100000 loop
update usr1
set user_name='aaaaaa',
usr_flag=0,
regist_time=to_date('2006-06-12 23:36:53','yyyy-mm-dd hh24:mi:ss')
where usr_onlyid=v_i;
commit;
end loop;
select to_char(sysdate,'sssss')
into v_time
from dual;
dbms_output.put_line(v_time);
end;
/

两表插入相差1/7,更新相差1/17.

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9183234/viewspace-909519/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9183234/viewspace-909519/