Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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;
share|improve this question
    
I have now have one table that contains item & location and the other is the full table. For every record in the first table the full table will get status update. I have tried merging & updating but the performance isn't improving. So now trying with the commit so if I somehow have to close the connection, the updated records can be removed from the first table and it then has to update less. Stupid I know but currently the only thing that we can do. –  Eve Jan 27 '12 at 12:39
    
have you tried to recreate table? That is to create another table with desired result, and then drop old table and rename the new table. Insert is better than update. –  Florin Ghita Jan 27 '12 at 12:47
    
Have you tried to split update big transaction in small transactions? Do a loop and for each iteration update n rows? Are you talking about n in your question? –  danihp Jan 27 '12 at 12:47
2  
You are updating rows one by one, is there a way to make set updates joining tables? –  danihp Jan 27 '12 at 13:09
3  
In your example, you are not using any content of table "dc_item_loc" to update table "item_loc". You are just setting string constants and whatever "get_vdate()" returns. To me it seems you could just do something like this: UPDATE item_loc il SET il.status = 'D', il.last_update_datetime = get_vdate, IL.LAST_UPDATE_ID = 'CNVOBJ_RNG' WHERE exists ( SELECT item, loc FROM dc_item_loc dcil WHERE dcil.item = il.item and dcil.loc = il.loc); –  Juergen Hartelt Jan 27 '12 at 13:21
show 4 more comments

migrated from stackoverflow.com Jan 27 '12 at 16:12

This question came from our site for professional and enthusiast programmers.

5 Answers

up vote 3 down vote accepted

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;
share|improve this answer
1  
Your Edit is a good ideea. It will do less context switches and the access will be fastest(using rowid.) I think this is best you can obtain with PL/SQL –  Florin Ghita Jan 27 '12 at 13:40
add comment

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 );
share|improve this answer
    
If you can do it in SQL, do it in SQL. If you can't do it in SQL, do it in PL/SQL. If you can't do it in PL/SQL, do it in the built-in JRE. If you can't do it in the JRE, do it external to the database. That first part is the important part, as you say: Don't use PL/SQL when SQL will suffice or performance will suffer an ugly horrid death. –  Rob Jan 30 '12 at 0:59
    
Yes I know but if it takes hours to run an update, with using sql. other options are needed and the anwser of Ollie is the best –  Eve Jan 30 '12 at 10:00
3  
@Eve - Updating 15 million rows in a single table shouldn't take hours. And converting a simple UPDATE SQL statement into PL/SQL shouldn't make it run faster. What else is going on? Are there triggers that are fired or unindexed foreign keys? –  Justin Cave Jan 30 '12 at 14:44
    
the only columns in dc_item_loc are item and loc –  Eve Feb 1 '12 at 10:08
add comment

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.

share|improve this answer
    
Wouldn't rowid mean that the item_loc table should have the same amount of records? Which isn't the case one 15 mil and the other has 21. –  Eve Jan 27 '12 at 13:38
    
No, it is the rowid of table item_loc. –  Florin Ghita Jan 27 '12 at 13:42
add comment

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.

share|improve this answer
1  
I couldn't agree more. The assumption that more frequent commits is a good idea is one of those dreaded "myths" that (unfortunately) will never die. Tom Kyte's point of view might be interesting here as well: asktom.oracle.com/pls/apex/… –  a_horse_with_no_name Feb 1 '12 at 0:33
add comment

Asktom is adamant:

A unit is work is never dictated by SIZE -- it is dictated by business requirements.

Committing frequently will only:

o increase the probability of an error o cause you to run slower o make your code more breakable

is that what the DBA wants to do? Make you run slower, with more errors and stand a chance of logically corrupting the data????

100k rows? that is toy sized -- small, small potatoes, nothing. I do tons more than that on my laptop.

Tell the DBA, Ok, I'll commit ever N rows -- N=10,000,000 -- no worries!

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:7661190956484

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.