6 Replies Latest reply: Sep 16, 2013 6:26 AM by Ishan RSS

    Need help fo update

    Muzz

      Hi Gurus

      I need your guidance in one scenario.

      I declare cursor ABC that contains almost half million records and then I put it into collection (PL/SQL table) and then by using bulk collect into and FORALL to insert data into my target table that is PART_QUALITIES.

      After inserting data into PART_QUALITIES table, I have almost half million records in it. Now I have to update another table that is not a part of my package by using PART_QUALITIES primary key and some certain conditions something like below:

       

      For x in (

                Select * from PART_QUALITIES

                where PART_TYPE='DEP' -- This statement return around 10 thousands records

                )

                Loop

                         update PART_INVOICES

                          set PART_INVOICES.PART_CODE =X.PART_CODE --(Primary key of PART_QUALITIES)

                          where PART_INVOICE.TYPE_DESC='ATTR';

                END LOOP;

      -----------

      My question is that whether I'm using right logic or wrong. Please guide Thanks

       

      Regards

      Shu

        • 1. Re: Need help fo update
          rp0428
          My question is that whether I'm using right logic or wrong.

          No - you are not.

           

          Are you trying to make it as slow as possible?

           

          Just use SQL to do the update. There is NOTHING in what you posted that indicates you need:

           

          1. a cursor ABC

          2. a collection of any sort

          3. a FORALL

          4. a PART_QUALITIES table

          5. a slow-by-slow FOR loop

           

          Write a SQL query that updates PART_INVOICES and uses a sub-query of data from whereever your 'cursor' gets it data from to populate the PART_QUALITIES table.

          • 2. Re: Need help fo update
            SomeoneElse

            I wholeheartedly agree with RP.

             

            Your code sounds way too complicated.  How about a simple merge:

             

            merge into part_invoices pi

            using (select *

                   from   part_qualities

                   where  part_type = 'DEP'

                  ) u

            on    (u.unknown_key = pi.unknown_key)

            when  matched then update

                  set pi.part_code = u.part_code

                  where pi.type_desc = 'ATTR';


            One problem, I can't tell from your code how you are associating a row in part_invoices with a row in part_qualities.  As it is, your code will not do what you think.

             

            I'll leave that up to you to fill in that blank.

            • 3. Re: Need help fo update
              Muzz

              Thanks for your answers and really appreciate your guidance . I forgot to mention 2 things in my update clause,  one is my associate key and the other one is exists clause. Please find below my code:

               

              For x in (

                        Select * from PART_QUALITIES

                        where PART_TYPE='DEP' -- This statement return around 10 thousands records

                        )

                        Loop

                                 update PART_INVOICES

                                  set PART_INVOICES.PART_CODE =X.PART_CODE --(Primary key of PART_QUALITIES)

                                  where PART_INVOICE.TYPE_DESC='ATTR'

                                   and exists  ( Select 1 from REPORT_RULES RR

                                                        Where RR.RULE_ID=PART_INVOICES.RULE_ID--(Primary key of REPORT_RULES)

                                                        and RR.GROUP_ID=X.GROUP_ID);

                        END LOOP;

              Actually, I'm using third table as a bridge to match associate key. I know I'm doing mess up but will use Merge statement as mentioned but as you see my coding I'm also using EXISTS command so my question is that is there any alternative available for Exists or I just need to put it in my where clause of Merge as it is. Appreciate your guidance.

               

              Regards

              Shu

              • 4. Re: Need help fo update
                Ishan

                May be: Untested.

                 

                merge into part_invoices pi

                using

                      ( select pq.part_code part_code, rr.rule_id rule_id

                        from part_qualities pq, report_rules rr

                        where pq.group_id = rr.group_id

                        and pq.part_type = 'DEP'

                      ) t

                on    ( pi.part_code = t.part_code and

                        pi.rule_id = t.rule_id

                      )

                when matched then

                  update set part_code = t.part_code

                  where pi.type_desc = 'ATTR'

                ;


                Thanks,

                Ishan

                • 5. Re: Need help fo update
                  Muzz

                  Hi

                   

                  Thanks for the MERGE  query and my package works with Merge but I have a couple of questions about  to clear my concepts.

                   

                  -- If I need to update less than 500 records then is this a good idea to use MERGE or regular for cursor like I use above can be a better option or loop with bulk collect is the better option. I did some research and my findings are :

                  Merge is  best and then Bulk collect and then for cursor. Please guide me and correct me if you find me wrong.

                   

                  In addition, if I move my last where clause with on clause something like below then does it effect my performance or not? :

                  Update Query

                  merge into part_invoices pi

                  using

                        ( select pq.part_code part_code, rr.rule_id rule_id

                          from part_qualities pq, report_rules rr

                          where pq.group_id = rr.group_id

                          and pq.part_type = 'DEP'

                        ) t

                  on    ( pi.part_code = t.part_code

                          and pi.rule_id = t.rule_id

                          and pi.type_desc = 'ATTR'--move from last line and add this clause here.

                        )

                  when matched then

                    update set part_code = t.part_code

                    --where pi.type_desc = 'ATTR'

                  ;

                   

                   

                  Thanks

                  Regards

                  Shu

                  • 6. Re: Need help fo update
                    Ishan
                    Merge is  best and then Bulk collect and then for cursor. Please guide me and correct me if you find me wrong.

                    You're right. Merge is faster then Bulk Collect. In fact, anything that can be done in SQL should not be done in PL SQL.

                    In addition, if I move my last where clause with on clause something like below then does it effect my performance or not? :

                    As, I am not aware of the data volume, indexes etc on all the tables, it's a bit difficult to guess the performance. Best you can do is to check the explain plan of both the queries and see if there's any change in both the plans.

                     

                    Hope it helps.

                     

                    Thanks,

                    Ishan