sql优化问题

Question

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

Answer 1

你的for循环中的i为什么在update语句里面不使用呢？那样for循环有什么意义？

Answer 2

detail表的primary key是什么？可以在update里面引用返回的primary key.

Answer 3

写漏了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 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;

Answer 4

#1 写漏了 应该是，可以去掉标红的地方吗
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;