Update within Pl/SQL block

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;

 

/
klpaytonAsked:
Who is Participating?
 
Naveen KumarConnect With a Mentor Production Manager / Application Support ManagerCommented:
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
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
 
sdstuberCommented:
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
sdstuberCommented:
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
 
klpaytonAuthor Commented:
Thanks!
0
 
sdstuberCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.