title: Improve performance of update where exists
body: <p>The below query is running forever. Can someone please tell me how to improve its performance? </p>
  
  <pre><code>    Query:  update fsa_ip_mth_end_fin_aggregate tbl2
      set (team_acip_gss_sls_cry_am,team_sr_gss_sls_cry_am)  =         
           (select acip_gss_sls_cry_am , sr_gss_sls_cry_am
           from (select agg.current_fsa_ip_id current_fsa_ip_id, 
                         sum(base1.sr_gss_sls_cry_am) sr_gss_sls_cry_am, 
                         sum(base1.acip_gss_sls_cry_am) acip_gss_sls_cry_am  
                  from ip_dim base,
                       ip_dim member,
                       ip_team_hierarchy_brdg,
                       fsa_ip_mth_end_fin_aggregate base1,
                       fsa_ip_mth_end_fin_aggregate agg 
                  where ip_team_hierarchy_brdg.base_ip_dim_id = base.ip_dim_id 
                  and ip_team_hierarchy_brdg.member_ip_dim_id = member.ip_dim_id 
                  and ip_team_hierarchy_brdg.active_in = 'Y' 
                  and base.dim_active_in = 'Y'
                  and member.dim_active_in = 'Y' 
                  and base1.current_fsa_ip_id = base.current_fsa_ip_id 
                  and agg.current_fsa_ip_id = member.current_fsa_ip_id 
                  and ip_team_hierarchy_brdg.allocation_factor != 0 
                  and agg.current_fsa_ip_id = base1.current_fsa_ip_id
                  group by agg.current_fsa_ip_id) tbl1
              where tbl1.current_fsa_ip_id = tbl2.current_fsa_ip_id)               
  where exists                  
  (select sr_gss_sls_cry_am,acip_gss_sls_cry_am 
           from (select agg.current_fsa_ip_id current_fsa_ip_id, 
                         sum(base1.sr_gss_sls_cry_am) sr_gss_sls_cry_am, 
                         sum(base1.acip_gss_sls_cry_am) acip_gss_sls_cry_am  
                  from ip_dim base,
                       ip_dim member,
                       ip_team_hierarchy_brdg,
                       fsa_ip_mth_end_fin_aggregate base1,
                       fsa_ip_mth_end_fin_aggregate agg 
                  where ip_team_hierarchy_brdg.base_ip_dim_id = base.ip_dim_id 
                  and ip_team_hierarchy_brdg.member_ip_dim_id = member.ip_dim_id 
                  and ip_team_hierarchy_brdg.active_in = 'Y' 
                  and base.dim_active_in = 'Y'
                  and member.dim_active_in = 'Y' 
                  and base1.current_fsa_ip_id = base.current_fsa_ip_id 
                  and agg.current_fsa_ip_id = member.current_fsa_ip_id 
                  and ip_team_hierarchy_brdg.allocation_factor != 0 
                  and agg.current_fsa_ip_id = base1.current_fsa_ip_id
                  group by agg.current_fsa_ip_id) tbl1
              where tbl1.current_fsa_ip_id = tbl2.current_fsa_ip_id);    
  </code></pre>
  
  <p>I tried the syntax: </p>
  
  <pre><code>    update (select query...) 
      set (team_acip_gss_sls_cry_am,team_sr_gss_sls_cry_am) = (select query...) 
  </code></pre>
  
  <p>But this did not work because of group by/aggregate functions involved. </p>
  
  <p>I also tried 'IN' instead of EXISTS. Both have almost the same performance. Please guide me in this regard. </p>