Oracle IF THEN query question

toooki
toooki used Ask the Experts™
on
My Oracle (11gR2) table t1 is huge. But the number of records is substantially less when t1.f2 = 'R'.
select count(*) from t1; -- outputs 500K records
select count(*) from t1 where t1.f2 = 'R'; -- outputs 2000 records

I have a query with this structure below.  The query part under update is huge but have the same construct as below. Is there any way I could avoid duplication of code as below under IF and ELSE? I do not want to use dynamic sql.
The query execution (hoping) is faster under IF as it is updating only 2000 records. And under the ELSE it is updating 500K records. The condition below will make the query faster for case when myFlag = 0.

IF  (myFlag = 0) THEN
      UPDATE t1 SET t1.f1 = 10 WHERE t1.f2 = 'R';

ELSE
      UPDATE t1 SET t1.f1 = 10;

END IF;

Thank you.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
flow01IT-specialist

Commented:
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
Geert GOracle dba
Top Expert 2009

Commented:
UPDATE t1
SET t1.f1 =
  case
    when myflag = 0 and t1.fr2 = 'R' then 10
  else
    10
  end;
flow01IT-specialist
Commented:
Geert, the CASE can be used but it should be in the where , not in the set

update t1 SET t1.f1 = 10
where CASE myflag
           WHEN 0 THEN 'R'
           ELSE
             t1.f2
           END  = t1.f2
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

Geert GOracle dba
Top Expert 2009

Commented:
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
Geert GOracle dba
Top Expert 2009

Commented:
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
flow01IT-specialist

Commented:
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.
flow01IT-specialist

Commented:
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.
awking00Information Technology Specialist

Commented:
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.

Author

Commented:
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.
Geert GOracle dba
Top Expert 2009

Commented:
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

Open in new window

Geert GOracle dba
Top Expert 2009

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

Author

Commented:
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.
Oracle dba
Top Expert 2009
Commented:
no, impossible to avoid the duplicate part
> actually this should have been the initial response

sometimes you end up with the same result after an analysis
> back to square 1

Author

Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial