This discussion is archived
6 Replies Latest reply: Sep 16, 2013 4:26 AM by Ishan RSS

Need help fo update

979596 Explorer
Currently Being Moderated

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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated

    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
    979596 Explorer
    Currently Being Moderated

    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 Journeyer
    Currently Being Moderated

    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
    979596 Explorer
    Currently Being Moderated

    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 Journeyer
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points