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 :-)