CODEWITHSUNDEEP
sqloracle-database
Marcos Fernandez
Marcos Fernandez

Reputation: 566

ORACLE update range of rows selecting them by rownum

I need to update a bunch of rows with this sentence:

UPDATE "STATISTICS" SET MEDIA = (CASE WHEN TB_STATISTICS.TYPE > 3 THEN 2 ELSE 1 END);

However it fails with ORA-30036 because there is not enough space for UNDOTBS1. I don't own the database and I cannot increase tablespace for undo.

The table has about 30 million rows and if I only update 10 million rows each time it works fine, with this:

UPDATE "STATISTICS" SET MEDIA = (CASE WHEN TB_STATISTICS.TYPE > 3 THEN 2 ELSE 1 END) where rownum < 10000000;

However, I cannot get it to update the next 10 million rows. I have tried this:

UPDATE "STATISTICS" SET MEDIA = (CASE WHEN TB_STATISTICS.TYPE > 3 THEN 2 ELSE 1 END) where rownum > 10000000 and rownum < 20000000;

But it always says "0 rows updated".

I have seen that when doing a SELECT, this can be accomplished by setting rownum as one of the selected columns and assigning and alias to it. But I don't know how I can do this in an UPDATE sentence.

Thx.

Upvotes: 1

Views: 4118

Answers (2)

Alex Poole
Alex Poole

Reputation: 191275

Your current condition is contradictory as a value cannot be less than 10 million and more than 20 million at the same time. But even if you reversed that it still wouldn't work as you expect, because of the way - and when - rownum is set. From the documentation:

Conditions testing for ROWNUM values greater than a positive integer are always false. For example, this query returns no rows:

SELECT *
  FROM employees
 WHERE ROWNUM > 1;

The first row fetched is assigned a ROWNUM of 1 and makes the condition false. The second row to be fetched is now the first row and is also assigned a ROWNUM of 1 and makes the condition false. All rows subsequently fail to satisfy the condition, so no rows are returned.

Using rownum for this isn't ideal, but if you change your statement to:

UPDATE "STATISTICS" SET MEDIA = (CASE WHEN TB_STATISTICS.TYPE > 3 THEN 2 ELSE 1 END)
where (MEDIA IS NULL OR  MEDIA != (CASE WHEN TB_STATISTICS.TYPE > 3 THEN 2 ELSE 1 END))
and rownum < 10000000 ;

then each time you run that it will exclude the rows you've already updated, or (as a bonus) which already had the right value anyway, because they will fail the where condition; but it allows for the value to initially be null too.

You could also do this in a PL/SQL block, preferably with bulk queries and updates, perhaps something like this is you have a unique or primary key field:

declare
  type t_tab_type is table of statistics%rowtype;
  l_tab t_tab_type;
  cursor c is select * from statistics;
begin
  open c;
  loop
  fetch c bulk collect into l_tab limit 10000000;
    forall i in 1..l_tab.count
      update statistics
      set media = case when l_tab(i).tb_statistics.type > 3 then 2 else 1 end
      where unique_key = l_tab(i).unique_key;
    commit;
    exit when c%notfound;
  end loop;
  close c;
end;
/

but probably with a smaller limit than ten million (and therefore more frequent commits, unless you track a separate variable to control that) as you don't want to use too much memory for the collection. You could also reduce the memory used by defining a record type and using that instead of %rowtype, possibly just with the unique key, and changing select * to just the specific columns that match the record type. Or a varray if you only get one column.

(Also not sure if tb_statistics.type is an object type, or more likely the actual table name and a column name, and something was missed modifying the statement for posting. If it is a column in the same table, and this calculation will always be the same as new rows are added, then media could potentially be a virtual column instead.)

It's better to have enough undo space to perform the operation in one transaction, both for efficiency and restartability; but it seems like this is a one-off so perhaps not worth getting that changed, even to a temporary large undo tablespace.

Upvotes: 3

Kaushik Nayak
Kaushik Nayak

Reputation: 31656

using ROWNUM is not always a reliable method for such operations. You may use an Implicit CURSOR LOOP like this if you have a UNIQUE or PRIMARY KEY column. Here I perform COMMIT every 100000 rows. You may modify it accordingly

DECLARE
  cnt INTEGER := 0;
BEGIN
  FOR rec_cur IN
  (
         SELECT unique_key_col,
                  CASE
                       WHEN tb_statistics.TYPE > 3 THEN 2
                       ELSE 1
                  END AS MEDIA
         FROM   "STATISTICS" )
  LOOP
    UPDATE "STATISTICS" s
    SET    s.media = rec_cur.media
    WHERE  s.unique_key_col = rec_cur.unique_key_col;

    IF cnt = 100000 THEN
      COMMIT;
      cnt := 0;
    ELSE
      cnt := cnt + 1;
    END IF;
  END LOOP;
  COMMIT;
END;
/

Upvotes: 2

Related Questions