Solved

Update within Pl/SQL block

Posted on 2014-01-15
7
620 Views
Last Modified: 2014-01-16
I asked a question earlier about updating a large data set of over 2 million records and the question was answered, but the solution took too long so I need to try a different way that is quicker. Basically I need the if_row_batch_num to be updated every 10000 rows..The first 10000 need to be 1000, the next 10000 should be 1001 and so on until the table is fully inserted. I think something is off with my loop because the first 10000 are being updated with 1000 but the next sets are not being updated correctly. Below is an example of my insert.



DECLARE

   CURSOR table_cur

   IS

      SELECT

             a.WEIGHT_MEASUREMENT,

             b.LAST_NAME,

             b.FIRST_NAME,

             b.MIDDLE_NAME,

             c.iNSTANCEID

        FROM    empl a

             JOIN

                Name b
             ON (a.emplid = b.emplid)

             join data c

             on (a.applicant_id=c.applicantid)

       WHERE b.name_type='P' AND last_name IS NOT NULL;

 

   TYPE typ_table_arr IS TABLE OF table_cur%ROWTYPE;

 

   l_table_arr   typ_table_arr;

   j number;

   z  number;

BEGIN

   OPEN table_cur;

 

j:=1000;

z:=0;

 

   LOOP

      FETCH table_cur

      BULK COLLECT INTO l_table_arr

      LIMIT 10000;

 

      EXIT WHEN l_table_arr.COUNT = 0;

 

      FORALL i IN l_table_arr.FIRST .. l_table_arr.LAST

         INSERT INTO contact (row_id,

                                             con_csn,

                                             if_row_batch_num,

                                             if_row_stat,

                                             con_last_name,

                                             Con_fst_name,

                                             con_mid_name,
                                           
                                             con_weight

                                             )

              VALUES (

                        conseq.NEXTVAL,

                        l_table_arr (i).INSTANCEID,

                        j,

                        'FOR_IMPORT',

                        'Person',

                        l_table_arr (i).WEIGHT_MEASUREMENT,

                        l_table_arr (i).LAST_NAME,

                        l_table_arr (i).FIRST_NAME,

                        l_table_arr (i).MIDDLE_NAME);

          z := z+1;

          if z = 10000 then

           z:=0;

          j:= j+1;

          end if;

      COMMIT;

   END LOOP;

END;

 

/
0
Comment
Question by:klpayton
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
7 Comments
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 39784421
i guess something wrong here.

 z := z+1;

          if z = 10000 then

           z:=0;

          j:= j+1;

          end if;



Can you dbms_output.put_line(..) debug messages and check the code flow by printing the values of z and j to see whether it is getting executed as you are expecting.
0
 
LVL 28

Accepted Solution

by:
Naveen Kumar earned 500 total points
ID: 39784425
ok..i think i found the problem. I am not sure why you need z variable in your code as you are not using that and to me it looks like it is not required. so try this out :

DECLARE

   CURSOR table_cur

   IS

      SELECT

             a.WEIGHT_MEASUREMENT,

             b.LAST_NAME,

             b.FIRST_NAME,

             b.MIDDLE_NAME,

             c.iNSTANCEID

        FROM    empl a

             JOIN

                Name b
             ON (a.emplid = b.emplid)

             join data c

             on (a.applicant_id=c.applicantid)

       WHERE b.name_type='P' AND last_name IS NOT NULL;

 

   TYPE typ_table_arr IS TABLE OF table_cur%ROWTYPE;

 

   l_table_arr   typ_table_arr;

   j number;

   z  number;

BEGIN

   OPEN table_cur;

 

j:=1000;



   LOOP

      FETCH table_cur

      BULK COLLECT INTO l_table_arr

      LIMIT 10000;

 

      EXIT WHEN l_table_arr.COUNT = 0;

 

      FORALL i IN l_table_arr.FIRST .. l_table_arr.LAST

         INSERT INTO contact (row_id,

                                             con_csn,

                                             if_row_batch_num,

                                             if_row_stat,

                                             con_last_name,

                                             Con_fst_name,

                                             con_mid_name,
                                           
                                             con_weight

                                             )

              VALUES (

                        conseq.NEXTVAL,

                        l_table_arr (i).INSTANCEID,

                        j,

                        'FOR_IMPORT',

                        'Person',

                        l_table_arr (i).WEIGHT_MEASUREMENT,

                        l_table_arr (i).LAST_NAME,

                        l_table_arr (i).FIRST_NAME,

                        l_table_arr (i).MIDDLE_NAME);

          j:= j+1;

      COMMIT;

   END LOOP;

END;
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39784456
You can't use pl/sql in a loop to execute faster than a single sql statement as in the previous question.

Think about it for a minute:  you want to update 2 million rows.  So, whatever work is involved in the actual update of those rows is the same  2 million changes is 2 million changes.  On top of the update effort ,  you also have the overhead of each sql statement and the overhead of the pl/sql loop itself.  Plus, sql  to pl/sql context switches.

Compare that to one sql,  same update effort, overhead of one sql vs multiple and 0 pl/sql overhead and no context switches.

PL/SQL will be slower in all cases.


Alternatively,  use the same sql as in the previous question, but segment it based on some criteria and have each segment increment

For example  in two different sessions execute these statements.

update yourtable set batch_num = 1000 + floor((rownum-1)/10000)  * 2
where some_column = 'X';

update yourtable set batch_num = 1001 + floor((rownum-1)/10000)  * 2
where some_column = 'Y';

If that's still not fast enough,  then create more segments to run in parallel.


Also,  2 million number updates is not a lot.  If it's taking an excessively long time, check if your session is being blocked by some other session that has one or more of the rows locked.  If that's the case pl/sql won't help there either.


And last,  the code in this question seems to be for a different problem entirely.  You're doing an inserts here.  The previous question was for updates.
0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 74

Expert Comment

by:sdstuber
ID: 39784517
One more thing...

your example code has 8 columns on the insert, but 9 values.
So, whether you do it in SQL with one statement (the good way) or looping in pl/sql (the bad way) it'll fail regardless.

Either you have too many values, or you're missing a column.
I took a guess that "Person" was an erroneous value

So, using a single statement your insert would look like this...  if my guess was wrong, simply add the missing column or rearrange the values as needed.  The idea is the same though.

INSERT INTO contact(
                row_id,
                con_csn,
                if_row_batch_num,
                if_row_stat,
                con_last_name,
                con_fst_name,
                con_mid_name,
                con_weight
            )
    SELECT conseq.NEXTVAL,
           c.instanceid,
           1000 + FLOOR((ROWNUM - 1) / 10000),
           'FOR_IMPORT',
           b.last_name,
           b.first_name,
           b.middle_name,
           a.weight_measurement
      FROM empl a
           JOIN name b ON (a.emplid = b.emplid)
           JOIN data c ON (a.applicant_id = c.applicantid)
     WHERE b.name_type = 'P' AND last_name IS NOT NULL;


again, if this isn't fast enough,  you can go parallel by splitting into pieces and running each piece in it's own session.

Looping inserts in pl/sql will always be slower for the same reasons as described above for the updates.
0
 

Author Closing Comment

by:klpayton
ID: 39785471
Thanks!
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39785536
klpayton,

I'm curious why you picked the looping method of multiple sql which is obviously not going to be as effecient as using a single sql?

If you have tested both and the looping was better, then I'm curious as to the method of testing because those results wouldn't make sense as described but maybe there are other criteria outside the scope of this thread that aren't being accounted for.
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Shared Service Environment 2 54
CRM 2011 Advanced Find Producing Error 3 40
Need more granular date groupings 4 44
Check for any ASM patches and install them. 1 23
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question