Bulk update with commit in oracle

Question

I am performing bulk update operation for a record of 1 million records. I need to COMMIT in between every 5000 records how can I perform?

update tab1 t1
   set (col1,col2,col3,col4)= 
   (select col1,col2,col3,col4 from tab_m where row_id= t1.row_id);

Why do you think you need to do a commit every 5K? https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4951966319022 — OldProgrammer, Mar 09 '17 at 14:29
Also see here https://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:6407993912330 — OldProgrammer, Mar 09 '17 at 14:35
If any exceptions comes, then I don't need to proceed with the records already updated — Thej, Mar 09 '17 at 14:35
And how do you know that a record was updated? Please read the links I posted, on why frequent commits are a bad practice. — OldProgrammer, Mar 09 '17 at 14:38
You did not say which version you are using but error_logging_clause have been around since at least 10g. And if you are on 10g and later i would use Merge in place of update which should help for performance because it will do a join between 2 tables and won't execute the subquery a million times (test it don't take my word for it as usual results may vary :)).http://stackoverflow.com/questions/26524519/continue-merge-after-exception — BulentB, Mar 09 '17 at 15:18

score 3 · Answer 1 · answered Mar 09 '17 at 15:13

If you are looking for a solution in PLSQL you can do it by using BULK INSERT/UPDATE as below:

  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;    
      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;

score 3 · Accepted Answer · answered Mar 10 '17 at 04:38

3

Per th question, if you only want to continue updating even if record fails with error logging then i think you should go with the DML error logging clause of Oracle. Hope this helps.

BEGIN
  DBMS_ERRLOG.CREATE_ERROR_LOG('TAB1');
  UPDATE tab1 t1
  SET
    (
      COL1,
      COL2,
      COL3,
      COL4
    )
    =
    (SELECT COL1,COL2,COL3,COL4 FROM TAB_M WHERE ROW_ID= T1.ROW_ID
    ) LOG ERRORS REJECT LIMITED UNLIMITED;

END;

answered Mar 10 '17 at 04:38

Avrajit Roy

3,233
1
13
25

Thank you for sharing this answer. I didn't know the `LOG ERRORS` clause. Unfortunately, it doesn't work for index violations, including foreign key violations, which is along with not null violations the number one error occuring in mass updates. If foreign keys are not a problem in OP's update, then this is a great and simple solution. – Thorsten Kettner Jun 15 '20 at 05:49

Bulk update with commit in oracle

2 Answers2