Solved

Update within Pl/SQL block

Posted on 2014-01-15
7
615 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
  • 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 73

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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 73

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 73

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to take different types of Oracle backups using RMAN.

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now