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

    Performance tuning needed for a query.

      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
                     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