ITPub博客

首页 > 数据库 > Oracle > Oracle Trigger 性能测试

Oracle Trigger 性能测试

原创 Oracle 作者:mckobe23 时间:2007-04-09 17:18:05 0 删除 编辑

前一段对用户表做了个触发器: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博客 ” ,链接:https://blog.itpub.net/9183234/viewspace-909519/ ,如需转载,请注明出处,否则将追究法律责任。

上一篇: 重启ORACLE
请登录后发表评论 登录
全部评论

注册时间:2009-06-12

  • 博文量
    40
  • 访问量
    587271