谁能帮我做下优化

[cbuser]

一条记录，如果存在username和subject 就修改，否则插入。
create table single (id varchar2(20),start_time varchar2(10),end_time varchar2(10),sum varchar2(10),username varchar2(10),subject varchar2(10));


create or replace procedure test_single(sid in string,starttime in string,endtime in string,sum1 in string,user in string,sub in string) as
begin
update single set start_time =starttime ,end_time=endtime, sum=sum1 where userName=user and subject=sub;
IF SQL%NOTFOUND THEN
INSERT INTO single(id,start_time,end_time,userName,subject,sum)
VALUES(sid,starttime,endtime,user,sub,sum1);

END IF;

commit;
end;
/


测试数据
call test_single('1234','1212','1214','wqeqw','qweqwe','zzzz');
call test_single('1235','1211','1214','zzzzz','qweqwe','zzzz');
call test_single('1236','1210','1214','eeeeeee','qweqwe','z333zz');
call test_single('1237','1209','1214','ewwww','qwewwwe','z3355');
call test_single('1238','12011','121114','1111www','qw11ewwe','z3355');
call test_single('1239','12041','121114','1111fww','qw1','z3355');
call test_single('1240','1041','1211314','11311dfww','wwww','z3355');
call test_single('1241','10421','222','222222','2222','4444444');


call test_single('1234','123312','1214','wq333eqw','qw33eqwe','zzzz');
call test_single('1235','133211','121334','zzzzz','qwe33qwe','zzzz');
call test_single('1236','123310','1214','eeeeeeee','qwe333qwe','z333zz');
call test_single('1237','1209','1214','ewwww','qwewq33we','z3355');
call test_single('1238','13332011','121114','1111ewwww','qw13331e','z3355');
call test_single('1239','12041','121114','1111eww','qw33311','z3355');
call test_single('1240','103341','1211314','11311e','qw33','z3355');
call test_single('1241','1033421','222','222222','2222','222');


call test_single('1234','444','4444','wq333eqw','qw33eqwe','zzzz');
call test_single('1235','13443211','121334','zzzzz','qwe33qwe','zzzz');
call test_single('1236','444444','1214','eeeeeeee','qwe333qwe','z333zz');
call test_single('1237','124449','1214','ewwww','qwewq33we','z3355');
call test_single('1238','1344011','121114','1111ewwww','qw13331e','z3355');
call test_single('1239','1244041','121114','1111','qw33311','z3355');
call test_single('1240','333','1211314','1131','qw33','z3355');
call test_single('1241','13333','222','222222','2222','222');

怎样通过修改存储过程，和shared_pool_size等参数来达到最快速度

[sanoul]

create table single (
id char(20),
start_time char(10),
end_time char(10),
sum char(10),
username varchar2(10),
subject varchar2(10)
);

create index idx_single_un on signle (username)
tablespace indx;

create index idx_single_subject on single (subject)
tablespace indx;

create or replace procedure test_single(
sid in string,
starttime in string,
endtime in string,
sum1 in string,
user in string,
sub in string) as
begin
update single
set start_time = starttime ,
end_time = endtime,
sum = sum1
where userName = user
and subject = sub;

IF SQL%NOTFOUND THEN
INSERT INTO single (id,start_time,end_time,userName,subject,sum)
VALUES (sid,starttime,endtime,user,sub,sum1);

END IF;

commit;
end;
/

[sanoul]

我看你的PL/Sql已经够优化了，没啥可改的

[skystar99047]

userName和subject字段建立复合索引

[sanoul]

另外传入参数用char就可以了

[szsmall]

create or replace procedure test_single(sid in string,starttime in string,endtime in string,
sum1 in string,user1 in string,sub in string) is
begin
merge into single b
using (select user1 sub from dual)
on (b.userName=user1 and b.subject=sub)
when matched then
update set start_time =starttime ,end_time=endtime, sum=sum1
when not matched then
INSERT values (sid,starttime,endtime,sum1,user1,sub);


end test_single;

[cbuser]

升级到9i，但是用上面的merge还是有问题，谁能帮我调试一下