Recently I was told to look into a piece of code which is taking 1 day to complete.


The Package basically uses MERGE statment to do updation or insertion based on different set of conditions

There are two MERGE statements in the proc. to two different tables, as you can see one of the subquery aliased 's'
in both the queries are same. I was thinking of creating a view for the derived table 's' so that queries become faster.

Is there any other suggestion so that I can rewrite these queries, keeping performance in mind. 

No I don't need anyone to rewrite the queries for me, I just want suggestions like whether using WITH clause in both the queries is possible or will a CURSOR here help. ( for the subquery which is common in both the queries).

-----------------------

MERGE into tab1 t
  UsING (Select distinct item_id 
                         , po_num
                         , po_line
                         , receiver
                         , cost_type
           from item_location y, 
         ( select c.item_id 
                 ,c.po_num
                 ,c.po_line
                 ,c.cost_type
                 ,c.receiver
                 ,c.update_date
           from facts c
               ,po_line a
           where c.item_id = a.item_id
             and c.po_num   = a.po_num
             and c.po_line  = a.po_line ) x
         where y.item_id = x.item_id  
         ) s
         ON ( t.dgp_flag = s.dgp_managed
                  AND t.onecost_costtype = s.cost_type
             )
         WHEN NOT MATCHED THEN 
         INSERT ( t.flag_id
                  ,t.dgp_flag
                  ,t.onecost_costtype
                 )
 
          VALUES ( seq_one.nextval
                   , s.dgp_managed
                   , s.cost_type
                 );



  MERGE into tab2 t
  UsING (Select            n.flag_id 
                         , m.cost_type
                         , m.dgp_managed
                         , m.po_num
                         , m.po_line
                         , m.item_id
                         , m.receiver
                         , m.location
                from receipts n, 
      (Select distinct item_id 
                       , po_num
                       , po_line
                       , receiver
                       , cost_type
          from item_location y, 
          ( select c.item_id 
                   ,c.po_num
                   ,c.po_line
                   ,c.cost_type
                   ,c.receiver
                   ,c.update_date
             from facts c
                  ,po_line a
             where c.item_id = a.item_id
               and c.po_num   = a.po_num
               and c.po_line  = a.po_line 
          ) x
           where y.item_id = x.item_id  
       ) s
       WHERE n.dgp_flag = s.dgp_managed
         AND n.onecost_costtype = s.cost_type ) M
           ON ( t.po_num = M.po_num
                AND t.po_num = M.po_num
                AND t.po_line = M.po_line
                AND t.item_id = M.item_id
                ANd t.receiver = M.receiver
              )
          WHEN MATCHED THEN
          update set flag_id = M.flag_id;