sql优化问题,想把红色部分删了,请帮我看看删了和不删结果是不是一样,这个sql数据量有可能达到1百万,对oracle优化不熟,各位解答一下,谢谢
declare
begin
for i in (select a.no
from detail a
where a.submit_date between date '2012-6-1' and date
'2012-12-31'
and a.code in
(select dr.child_code
from code_relation dr
where dr.parent_code = '288'
union
select t.specail_code
from specail_general_relation t
where t.general_code in
(select dr.child_code
from code_relation dr
where dr.parent_code = '288'))) loop
update detail a
set a.amount = (select max(b.amount) --日常展业费
from OTHER_DETAIL b
where a.no = b.no
and b.other_no is null)
where a.submit_date between date '2012-6-1' and date
'2012-12-31'
and
and a.code in (select dr.child_code
from code_relation dr
where dr.parent_code = '288'
union
select t.specail_code
from specail_general_relation t
where t.general_code in
(select dr.child_code
from code_relation dr
where dr.parent_code = '288')) and a.no = i.no
and exists (select 1
from OTHER__DETAIL b
where a.no = b.no
and b.other_no is null);
end loop;
end;
优化sqloracle
declare
begin
for i in (select a.no
from detail a
where a.submit_date between date '2012-6-1' and date
'2012-12-31'
and a.code in
(select dr.child_code
from code_relation dr
where dr.parent_code = '288'
union
select t.specail_code
from specail_general_relation t
where t.general_code in
(select dr.child_code
from code_relation dr
where dr.parent_code = '288'))) loop
update detail a
set a.amount = (select max(b.amount) --日常展业费
from OTHER_DETAIL b
where a.no = b.no
and b.other_no is null)
where a.submit_date between date '2012-6-1' and date
'2012-12-31'
and
and a.code in (select dr.child_code
from code_relation dr
where dr.parent_code = '288'
union
select t.specail_code
from specail_general_relation t
where t.general_code in
(select dr.child_code
from code_relation dr
where dr.parent_code = '288')) and a.no = i.no
and exists (select 1
from OTHER__DETAIL b
where a.no = b.no
and b.other_no is null);
end loop;
end;
优化sqloracle
begin
for i in (select a.no
from detail a
where a.submit_date between date '2012-6-1' and date
'2012-12-31'
and a.code in
(select dr.child_code
from code_relation dr
where dr.parent_code = '288'
union
select t.specail_code
from specail_general_relation t
where t.general_code in
(select dr.child_code
from code_relation dr
where dr.parent_code = '288'))) loop
update detail a
set a.amount = (select max(b.amount) --日常展业费
from OTHER_DETAIL b
where a.no = b.no
and b.other_no is null)
where a.submit_date between date '2012-6-1' and date
'2012-12-31'
and a.no=i.no
and a.code in (select dr.child_code
from code_relation dr
where dr.parent_code = '288'
union
select t.specail_code
from specail_general_relation t
where t.general_code in
(select dr.child_code
from code_relation dr
where dr.parent_code = '288')) and a.no = i.no
and exists (select 1
from OTHER__DETAIL b
where a.no = b.no
and b.other_no is null);
end loop;
end;
declare
begin
for i in (select a.no
from detail a
where a.submit_date between date '2012-6-1' and date
'2012-12-31'
and a.code in
(select dr.child_code
from code_relation dr
where dr.parent_code = '2'
union
select t.specail_code
from specail_general_relation t
where t.general_code in
(select dr.child_code
from code_relation dr
where dr.parent_code = '2'))) loop
update detail a
set a.amount = (select max(b.amount) --日常展业费
from OTHER_DETAIL b
where a.no = b.no
and b.other_no is null)
where a.submit_date between date '2012-6-1' and date
'2012-12-31'
and a.no=i.no
and a.code in (select dr.child_code
from code_relation dr
where dr.parent_code = '288'
union
select t.specail_code
from specail_general_relation t
where t.general_code in
(select dr.child_code
from code_relation dr
where dr.parent_code = '288')) and a.no = i.no
and exists (select 1
from OTHER__DETAIL b
where a.no = b.no
and b.other_no is null);
end loop;
end;