Oracle IF THEN query question

If it's done in pl/sql

update t1 SET t1.f1 = 10
where (myFlag =  0 and t1.f2 = 'R')
           OR
           (myFlag <> 0)

In your execution plan you problaby wil see 2 queries expanded : the distincting condition however wil be evaluated high in the hierarchy and weed out one branch during execution

UPDATE t1
SET t1.f1 =
  case
    when myflag = 0 and t1.fr2 = 'R' then 10
  else
    10
  end;

well, i've been reading your and my approach ... a few times
ah... you've added another approach

and actually i keep on ending on the same idea
> for performance it would be better with 2 statements

the set case should have been this:

UPDATE t1
SET t1.f1 =
  case
    when myflag = 0 and t1.fr2 = 'R' then 10
    when myflag <> 0 then 10
  else
    t1.f1
  end;

but it would update every row of the table
unless .. a where is added too

UPDATE t1
SET t1.f1 =
  case
    when myflag = 0 and t1.fr2 = 'R' then 10
    when myflag <> 0 then 10
  else
    t1.f1
  end
where
  t1.f1 <>
  case
    when myflag = 0 and t1.fr2 = 'R' then 10
    when myflag <> 0 then 10
  else
    t1.f1
  end

The performance penalty for joining them by an OR as in my first post will be neglectable as compared to 2 separate statements: the explain plan however will have a branch with a full table.  But when myflag = 0 only the other branch will add to the workload at execution , the workload for the brach with the full table is neglectable (because the primairy condition failes).

For the solution in my second post I don't know even if the performance gain 'tooki' aimes at with the 'R' filter will be satisfied : so test it or don't use it: it was mend as an example of using the CASE in a WHEN clause.

Geert : It's not just another approach : your solution does not do the right thing :  if myflag = 0 it will still update all records in the table with value 10 where it should only update the records with the 'R'  in it.

The last section of post ID: 37859620 (not the right thing) referred to post 37859467.

Te last section of Post 37859597 will work as far as i can see.

Post crossed will typing.

I'm not sure what it is you're really trying to do. Are you saying you want to update all of the f1 fields where f2 does not equal 'R' and only those where f2 does equal 'R' and myFlag = 0? Perhaps you could provide some sample data where myFlag = 0 and where myFlag does not equal 0 and f2 equals 'R' and f2 equals something else and what you want it to be after the update.

Thank you all. Sorry I tried to put my question in a simplified way but I could not. This is the exact query that I am running now and it is the working code.

Yes it is within a Pl/sql procedure.

IF  (myFlag = 0) THEN
      UPDATE t1
         SET t1.f1 =
             (SELECT val1
                FROM someTable1
               WHERE somefield1 = 100
                 AND somefield2 = t1.f9)
                 WHERE t1.f2 = 'R';
ELSE
     UPDATE t1
         SET t1.f1 =
             (SELECT val1
                FROM someTable1
               WHERE somefield1 = 100
                 AND somefield2 = t1.f9);
END IF;

The part of the query block within the brackets () are identical under IF and under ELSE. I wanted to see if I could eliminate the repeat. If I will change the rule later say change the number from 100 to 200 -- I need to do that in two places.


The rule is, Under IF (flag = 0):
Update f1 only for records where t1.f2 = 'R';

Under ELSE (flag <> 0):
Update all records of f1;


I am hoping here that the query under IF is faster than the query under ELSE. If it goes under IF part, it updates 2000 records. If it goes under ELSE part, it updates 500,000 records.

 Thank you.

you can use a second parameter for the 100

but you should add a where to limit the update to effective changes only

declare 
  v_value NUMBER;
begin
  v_value := 100;

  UPDATE t1 a
  SET t1.f1 =
    case
      when myflag = 0 and a.fr2 = 'R' then 
        (SELECT b1.val1
          FROM someTable1 b1
          WHERE b1.somefield1 = v_value
           AND b1.somefield2 = a.f9)
      when myflag <> 0 then 
        (SELECT b2.val1
         FROM someTable1 b2
               WHERE b2.somefield1 = v_value
                 AND b2.somefield2 = a.f9)
    end
where
  t1.f1 <>
  case
      when myflag = 0 and a.fr2 = 'R' then 
        (SELECT b3.val1
          FROM someTable1 b3
          WHERE b3.somefield1 = v_value
           AND b3.somefield2 = a.f9)
      when myflag <> 0 then 
        (SELECT b4.val1
         FROM someTable1 b4
               WHERE b4.somefield1 = v_value
                 AND b4.somefield2 = a.f9)
    end

Select allOpen in new window

in the end ...
your statement with the if doesn't look as complex

Thank you. But I have question.

I duplicate this part of the code:
             (SELECT val1
                FROM someTable1
               WHERE somefield1 = 100
                 AND somefield2 = t1.f9)
In my post 37863447 under the IF and ELSE. The code you mentioned above does not seem to eliminate the duplicate part....
Thanks.

Thank you for the final comment... that it is not possible this way. I understand now. I tried a different approach instead of using the two sets of query blocks. But that is very much business logic specific way.
Thanks a lot for help.