Forum Stats

  • 3,727,807 Users
  • 2,245,471 Discussions
  • 7,853,047 Comments

Discussions

update in bulk

Tony007
Tony007 Member Posts: 4,059 Bronze Trophy
edited November 2019 in SQL & PL/SQL

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 EXCEPTIONSUPDATE 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;
Jonathan LewisL. Fernigrini

Answers

  • L. Fernigrini
    L. Fernigrini Data Engineer Sr Consultant Member Posts: 3,529 Bronze Crown
    edited November 2019

    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

  • Tony007
    Tony007 Member Posts: 4,059 Bronze Trophy
    edited November 2019

    if i do like that am geting

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

  • L. Fernigrini
    L. Fernigrini Data Engineer Sr Consultant Member Posts: 3,529 Bronze Crown
    edited November 2019

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

    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.

  • L. Fernigrini
    L. Fernigrini Data Engineer Sr Consultant Member Posts: 3,529 Bronze Crown
    edited November 2019

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

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

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,579 Gold Crown
    edited November 2019

    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

  • L. Fernigrini
    L. Fernigrini Data Engineer Sr Consultant Member Posts: 3,529 Bronze Crown
    edited November 2019

    Jonathan

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

    Jonathan Lewis
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,579 Gold Crown
    edited November 2019
    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

    L. Fernigrini
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,579 Gold Crown
    edited November 2019
    L. Fernigrini wrote:JonathanThe 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

  • L. Fernigrini
    L. Fernigrini Data Engineer Sr Consultant Member Posts: 3,529 Bronze Crown
    edited November 2019

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

Sign In or Register to comment.