Good day "Toms",

I am working on a project which performs bulk updates of millions of rows. Unfortunately each update is currently a separate update statement which updates 2 columns with unique values and uses a where clause with a unique ID to identify the row to be updated.

Every update will be reparsed because the values are literals.
If I define them as variables before each update, will this actually save time, or is there a better way? I am stumped. VALUE1, VALUE2 and VALUE3 are all supplied, not from inside the database.

The statement is:

UPDATE TABLE1 SET COL1 = 'VALUE1',COL2 = 'VALUE2' WHERE COL3 = 'VALUE3';

I am looking at:

define var1='VALUE1'
define var2='VALUE2'
define var3='VALUE3'
UPDATE TABLE1 SET COL1 = '&var1',COL2 = '&var2' WHERE COL3 = '&var3';

But I would have to redefine the vars for each update and feel this will negate the gain of having the update statement cached.
I was really hoping to use FORALL, but can't make it work with all external supplied values. Any suggestions?

create table t (
  c1 int, c2 int, c3 int
);

insert into t 
  select level, level, level 
  from   dual
  connect by level <= 5;

commit;

select * from t;

C1     C2     C3   
    1     1     1 
    2     2     2 
    3     3     3 
    4     4     4 
    5     5     5 

declare
  ids dbms_sql.number_table;
  v2  int := 0;
  v3  int := 99;
begin
  for i in 1 .. 3 loop
    ids ( i ) := i;
  end loop;
  
  forall rws in 1 .. ids.last
    update t
    set    c2 = v2, c3 = v3
    where  c1 = ids ( rws );

end;
/

select * from t;

C1     C2     C3   
    1     0    99 
    2     0    99 
    3     0    99 
    4     4     4 
    5     5     5