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';