【性能优化】求大神们帮看看这个存储过程怎么优化 说明:table1,table2,table3几个表都是几千万的数据量,性能上好象有点问题,对oracle不是很熟,求优化方案
begin
declare
cursor cur_3 is select * from table1
where trunc(createtime) in
(select trunc(createtime) from table2 where........);
type t_table is table of table1%rowTYPE;
c_table t_table;
begin
open cur_3;
------------loop1
loop
fetch cur_3 bulk collect into c_table limit 1000;
exit when c_table.count = 0;
---------------loop2
for r in c_table.first..c_table.last loop
select 字段1,字段2........
into 参数1.参数2......
from t_user where userid= c_table(r).UserID;
-------------loop 3
for de in (select * from table3 where **ID=c_table(r).**ID order by ******) loop
----又长又臭的业务表操作
end loop;
end loop;
end loop;
commit;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
close cur_3;
end ------解决方案--------------------