9 Replies Latest reply on Nov 14, 2019 6:52 PM by L. Fernigrini

    update in bulk

    Tony007

      hi how can i update records within one am having 6 million records to update, i try this am in 12c database

      DECLARE
             c_limit PLS_INTEGER := 100;
      
      
             CURSOR employees_cur
             IS
                SELECT employee_id
                  FROM employees
                 WHERE department_id = department_id_in;
      
      
             TYPE employee_ids_t IS TABLE OF  employees.employee_id%TYPE;
      
      
             l_employee_ids   employee_ids_t;
          BEGIN
             OPEN employees_cur;
      
      
             LOOP
                FETCH employees_cur
                BULK COLLECT INTO l_employee_ids
                LIMIT c_limit;      -- This will make sure that every iteration has 100 records selected
      
      
                EXIT WHEN l_employee_ids.COUNT = 0;           
      
      
              FORALL indx IN 1 .. l_employee_ids.COUNT SAVE EXCEPTIONS
                UPDATE employees emp  -- Updating 100 records at 1 go.
                   SET emp.salary =
                          emp.salary + emp.salary * increase_pct_in
                 WHERE emp.employee_id = l_employee_ids(indx);
      commit;
      
      FORALL indx IN 1 .. l_employee_ids.COUNT SAVE EXCEPTIONS
      UPDATE employees_details emp  -- Updating 100 records at 1 go.
                   SET emp.salary =
                          emp.salary + emp.salary * increase_pct_in
                 WHERE emp.employee_id = l_employee_ids(indx);
      
      
      
            commit;    
            END LOOP;
      
      
          EXCEPTION
             WHEN OTHERS
             THEN
                IF SQLCODE = -24381
                THEN
                   FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
                   LOOP
                       -- Caputring errors occured during update
                      DBMS_OUTPUT.put_line (
                            SQL%BULK_EXCEPTIONS (indx).ERROR_INDEX
                         || ‘: ‘
                         || SQL%BULK_EXCEPTIONS (indx).ERROR_CODE);
      
      
                       --<You can inset the error records to a table here>
      
      
      
      
                   END LOOP;
                ELSE
                   RAISE;
                END IF;
          END;
      
        • 1. Re: update in bulk
          L. Fernigrini

          Why do it on PL/SQL if it can be directly done in SQL??

           

          UPDATE employees emp

                  SET emp.salary = emp.salary + emp.salary * increase_pct_in

          WHERE department_id = department_id_in;

           

          UPDATE employees_details emp

               SET emp.salary = emp.salary + emp.salary * increase_pct_in

          WHERE department_id = department_id_in;

           

           

          If you need to log errors rather than aborting the update, you can use Log Errors, as explaided here:

           

          https://oracle-base.com/articles/10g/dml-error-logging-10gr2#update

          • 2. Re: update in bulk
            Tony007

            if i do like that am geting

            ORA-20220: ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes

            • 3. Re: update in bulk
              L. Fernigrini

              That error is because you use DBMS_OUTPUT and put more information than it supports...

              Re: ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes

               

               

              It has NOTHING to do with the UPDATE.

               

              Get rid of all the PL/SQL code, you should just have the UPDATE (with the LOG ERRORS clause if required) as I mentioned above.

              • 4. Re: update in bulk
                L. Fernigrini

                And here is a great articule on how to (and how not to) handle exceptions:

                https://blogs.oracle.com/oraclemagazine/error-management

                • 5. Re: update in bulk
                  Jonathan Lewis

                  You've got the update twice - so a double increase for all employees

                   

                  The critical error is that your EXCEPTION is in the wrong place.  At present your "for all .. save exceptions" is going to update 100 employees each time it goes around the loop without getting an error. The first time the bulk update raises an error Oracle will jump OUT OF THE LOOP to the exceptions clause, and report any errors that appeared in that batch - then the pl/sql block is complete and no more updates take place.

                   

                  The code INSIDE the main loop should read something like:

                   

                  begin

                     for all .. save exceptions

                          update ....;

                   

                    exception

                      when others then

                        if SQLCODE = -24381 then

                         etc.

                  end;

                   

                   

                   

                  In fact, it would be much better to declare an named exception for the -24381, then your exception handler could read:

                     exception

                         when my_exception then

                             ....

                         when others then raise;

                   

                   

                  There's some sample code on my blog :  https://jonathanlewis.wordpress.com/2016/06/24/never/

                   

                  Regards

                  Jonathan Lewis

                  • 6. Re: update in bulk
                    L. Fernigrini

                    Jonathan

                     

                    The second update is on "employees_details" table, so no double increase is granted

                    • 7. Re: update in bulk
                      Jonathan Lewis

                      L. Fernigrini wrote:

                       

                      And here is a great articule on how to (and how not to) handle exceptions:

                      https://blogs.oracle.com/oraclemagazine/error-management

                       

                      Nice article, as usual from Tim.

                      An important part of it is the comparison of performance between different versions of Oracle at the end, which shows the defect in "log errors" finally disappearing in 12.1,  However, I've just run one of my standard tests (see the blog note I referenced) on 19.3 and got the following time comparing an insert of 1M rows with 6 uniqueness errors:

                       

                      LOG ERRORS:  37.00 - 40.00 seconds

                      FORALL .. SAVE EXCEPTIONS:   15 - 27 seconds

                       

                      For reasons I haven't tracked down yet the the single statement update results in more frequent log file switches even though  virtually all the stats (including the redo size) is very similar.  Choosing between the two strategies is not a trivial option.

                       

                      Regards

                      Jonathan Lewis

                      • 8. Re: update in bulk
                        Jonathan Lewis

                        L. Fernigrini wrote:

                         

                        Jonathan

                         

                        The second update is on "employees_details" table, so no double increase is granted

                         

                        Thanks, I missed that detail (no pun intended)

                         

                        Naming convention rule N: "Don't give different tables the same alias".

                         

                         

                        Regards

                        Jonathan Lewis

                        • 9. Re: update in bulk
                          L. Fernigrini

                          I've re-read your article (I remember reading it some time ago) and agree that not always is OK to say "DO NOT DO ON PL/SQL WHAT YOU CAN DO ON SQL".

                           

                          I wondered about the performance scenario with an update rather than Insert, and different types of failures (Foreign Key, Unique Key, Check, ilegal op like x/0, etc etc).

                           

                          Will try to conduct some test on 12c and 19c as soon as I have some free time :-)