1 Reply Latest reply: Nov 10, 2011 1:42 AM by Johnreardon RSS

    Performance tuning needed for a query.

    662043
      Hi all,

      I am facing problem to extract data and insert into another table. I need to fetch part number from all_parts_already and then based on the result I need to extract place from all_parts and insert into all_parts_already.

      Sample data in the tables:

      table all_parts_already:

      part part_desc technique company place

      1 A Engine TVS B1
      1 Av Engine TVS B2
      1 Ab Engine TVS B3
      2 Ah Engine TVS B3
      2 Ap Engine TVS B2


      table all_parts:

      technique company place
      Engine TVS B1
      Kim TVS B2
      Engine TVS B3
      Engine TVS B4
      XXXXX TVS B5
      Engine TVS B6



      for c1 in (select distinct parts from all_parts_already where
      technique = 'Engine' and
      Company = 'TVS' ) loop
           
           for c2 in (select distinct place from all_parts where
                     technique = 'Engine' and
                     Company ='TVS' and
                     minus
                     select distinct Place from all_parts_already where
                     technique = 'Engine' and
                     Company = 'TVS' and
                     parts = c1.parts ) loop
                                    
                     insert into all_parts_already (select c2.place,place_desc,c1.parts,c2.place from place_master where parts=c1.parts and place=c2.place);

           end loop;          

      end loop;


      the data i am dealing with is in millions. One technique may have 1000 parts. One part may have 500 places. So the loop runs that many times creating the delay.

      Please tell me how to move forward.I am getting the output i need but the time it takes is too much(goes on to days)

      Thanks a lot

      :)