Frequent or Infrequent Commit

Question

I'm trying to run an update query but still taking to long I have a commit in it. Is it better to set the commit to a higher value (5000) or lower (1)? I have to update 15 million records in a table of 21 million

edit here's my query for the update but I need to know how low/high I should set the commit

DECLARE
CURSOR c_itemloc
IS
  SELECT item, loc 
  FROM dc_item_loc;

 l_item item_loc.item%TYPE;
 l_loc item_loc.loc%TYPE;
 i        NUMBER;
 l_commit VARCHAR2(1) := 'Y';

 BEGIN
   i:=0;
   FOR r_itemloc IN c_itemloc
   LOOP
     i := i+1;
     UPDATE item_loc il
     SET il.status             = 'D',
        il.last_update_datetime = get_vdate,
        IL.LAST_UPDATE_ID       = 'CNVOBJ_RNG'
     WHERE item                = r_itemloc.item
          AND loc                   = r_itemloc.loc;

     IF l_commit = 'Y' AND mod(i, 5000) = 0  THEN 
        COMMIT; 
     END IF;

    END LOOP;

  EXCEPTION
  WHEN OTHERS THEN
   dbms_output.put_line('YOU FAIL');
  END;

Answer 1

Not know a huge amounts about what version (I've assumed 11g) you are using and other environment issues, something like this might be useful.

I'd set the commit level to be as high as your environment can comfortably handle as the fewer commits the faster it will be. What you can confortably handle will be dictated by factors such as:

• Are other users using the system
• Memory available
• Are you running anything else whilst this is running etc.

You can alter the constant c_bulk_limit to whatever size you can cope with.

DECLARE
   c_bulk_limit CONSTANT PLS_INTEGER := 5000;
   --
   CURSOR c_itemloc
   IS
      SELECT item, 
             loc 
        FROM dc_item_loc;
   --
   TYPE itemloctype IS TABLE OF c_itemloc%ROWTYPE
        INDEX BY PLS_INTEGER;
   itemloc_tab itemloctype;
BEGIN
   -- Open cursor
   OPEN c_itemloc;
   LOOP
      -- Fetch bulk data
      FETCH c_itemloc BULK COLLECT INTO itemloc_tab
      LIMIT c_bulk_limit;

      FORALL x IN INDICES OF itemloc_tab
         UPDATE item_loc il
            SET il.status = 'D',
                il.last_update_datetime = get_vdate,
                il.last_update_id = 'CNVOBJ_RNG'
          WHERE il.item = itemloc_tab(x).item 
            AND il.loc  = itemloc_tab(x).loc;

      -- Commit every c_bulk_limit records
      COMMIT;

      -- Loop exit criteria
      EXIT WHEN itemloc_tab.COUNT < c_bulk_limit;

   END LOOP;
   CLOSE c_itemloc;

EXCEPTION
   WHEN OTHERS
   THEN
      IF c_itemloc%ISOPEN
      THEN
         CLOSE c_itemloc;
      END IF;
      --
      DBMS_OUTPUT.put_line ('YOU FAIL');
END;

At the very least you'll be using more bulk operations and therefore significantly less context switching so it should save you time.

Of course the best method would be a single UPDATE statement but with that many records your DB might not be able to cope without interim commits.

EDIT: Having just seen @Florin's answer, it might be a good idea to use his cursor to get the ROWID of the taget update table before then using the BULK commands to perform the update as it will be the fastest access method.

E.G.

DECLARE
   c_bulk_limit CONSTANT PLS_INTEGER := 5000;
   --
   CURSOR c_itemloc
   IS
      SELECT --+ use_hash(d i)     
             i.rowid rid  
        FROM dc_item_loc d   
        JOIN item_loc i on (i.item= d.item and i.loc = d.loc) ; 
   --
   itemloc_tab DBMS_SQL.UROWID_TABLE;
BEGIN
   -- Open cursor
   OPEN c_itemloc;
   LOOP
      -- Fetch bulk data
      FETCH c_itemloc BULK COLLECT INTO itemloc_tab
      LIMIT c_bulk_limit;

      FORALL x IN INDICES OF itemloc_tab
         UPDATE item_loc
            SET status               = 'D',
                last_update_datetime = get_vdate,
                last_update_id       = 'CNVOBJ_RNG'
          WHERE rowid = itemloc_tab(x);   

      -- Commit every c_bulk_limit records
      COMMIT;

      -- Loop exit criteria
      EXIT WHEN itemloc_tab.COUNT < c_bulk_limit;

   END LOOP;
   CLOSE c_itemloc;

EXCEPTION
   WHEN OTHERS
   THEN
      IF c_itemloc%ISOPEN
      THEN
         CLOSE c_itemloc;
      END IF;
      --
      DBMS_OUTPUT.put_line ('YOU FAIL');
END;

Answer 2

Why are you doing this in PL/SQL in the first place? The most efficient way to do anything in an Oracle database should be to do it in SQL

 UPDATE item_loc il
 SET il.status             = 'D',
    il.last_update_datetime = get_vdate,
    IL.LAST_UPDATE_ID       = 'CNVOBJ_RNG'
 WHERE EXISTS( SELECT 1
                 FROM dc_item_loc d
                WHERE d.item = il.item
                  AND d.loc  = il.loc );

Answer 3

The problem isn't the commit. Commit is much faster than updates. You can commit at 100k. It is not a problem if there is undo space.

Best is if you can do it in SQL. we don't have enough information to do it.

However, you can improve the code speed eliminating the overhead of searching in the table item_loc:

DECLARE
CURSOR c_itemloc
IS
  SELECT --+ use_hash(d i)
    i.rowid rid 
    --, d.item as d_item, d.loc  as d_loc, i.item as i_item, i.loc as i_loc
  FROM dc_item_loc d
  JOIN item_loc i on (i.item= d.item and i.loc = d.loc) ;

 BEGIN

   i:=0;

   FOR r_itemloc IN c_itemloc
   LOOP

     i := i+1;

     UPDATE item_loc il
     SET il.status             = 'D',
         il.last_update_datetime = get_vdate,
         IL.LAST_UPDATE_ID       = 'CNVOBJ_RNG'
     WHERE rowid = r_item_loc.rid;

     IF mod(i, 5000) = 0 THEN
        COMMIT ;
     END IF;
   END LOOP;

 EXCEPTION
 WHEN OTHERS THEN
   -- the error must be treated better.
   dbms_output.put_line('YOU FAIL');
 END;

Another thing on you may consider is the restartabily in case of failure. this procedure will do the work again if restarted.

Answer 4

Commit only when you want to end a transaction e.g. when you complete a logical unit of work.

Commiting X number of rows will be 1/ slower, 2/ use more undo, and most importantly 3/ possibly makes your process non-restartable.