?
Solved

Update within Pl/SQL block

Posted on 2014-01-15
7
Medium Priority
?
622 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 2000 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
Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

752 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