Commit Interval

This article is about Inserting, Deleting or Updating millions of records to and from an existing table using  database best practices. Why can't we just insert/delete/update like we have been taught in school. With any DBMS, the database log writes all the changes that happens when executing Data Manipulation Language statements. Inserting or updating millions records without a commit will make the log grow big which may fill the disk space causing a database to go down. To prevent redo log filling the disk space you will need a frequent commit interval. The commit statement truncates the redo-log file.

I would use commit interval if I am Updating/Inserting/Updating 50 K or more records. How do you determine the commit interval? I normally pick 10 K with my DBMS system but your system DBA is the best person to ask for commit interval. Remember issuing a frequent commit statement is also not a healthy practice for a DBMS therefore you should consult with your DBA.

Example 1:  This example demonstrates an insert to a table with a commit interval of 1000.

DECLARE 
    CURSOR my_cursor IS 
      SELECT id 
      FROM   employee; -- replace this with your select statement 
    commit_count NUMBER := 0; 
BEGIN 
    FOR cur_record IN my_cursor LOOP 
        INSERT INTO employee_test 
                    (id) 
        VALUES      (cur_record.id ); 
        -- replace this with your insert/ statement. 
        commit_count := commit_count + 1; 
        IF MOD(commit_count, 1000) = 0 THEN 
          COMMIT; 
        END IF; 
    END LOOP; 
END; 

Similarly, the above INSERT statement of PL/SQL block can be replaced with a DELETE or UPDATE statement.  Below, we will demonstrate an example with a DELETE statement.

Example 2 :

DECLARE 
  v_count NUMBER; 
BEGIN 
  LOOP 
    SELECT Count(*) 
    INTO   v_count 
    FROM   hardware_admin.hardware_inventory 
    WHERE  code = 'HD0319';
    IF v_count > 0 THEN 
      DELETE 
      FROM   hardware_admin.pfx_ks_staging 
      WHERE  code = 'HD0319'; 
      AND 
      ROWNUM <= 1000; 
      COMMIT; 
    ELSE 
      EXIT; 
    END IF; 
  END LOOP; 
END;

There are more ways to get this done, I found one other ways to get this done. See example below.

DECLARE 
  count NUMBER := 0; 
  total NUMBER := 0; 
  CURSOR del_record_cur IS 
    SELECT ROWID 
    FROM   <owner>.<table_name> 
    WHERE  <your_criteria> BEGIN FOR rec IN del_record_cur LOOP 
    DELETE 
    FROM   <owner>.<table_name> 
    WHERE  ROWID = rec.ROWID; 
total := total + 1; 
count := count + 1; 
IF (count >= 1000) THEN 
  COMMIT; 
  count := 0; 
END IF; 
END LOOP; 
COMMIT; 
dbms_output.put_line('Deleted ' 
|| total 
|| ' records from <OWNER>.<TABLE_NAME>.'); 
END;/

The choice is yours, out of these examples, I personally like the last example of using cursor. Which one do you like and why?