instead of:
for x in ( select * from t1 )
loop
update t2 set ..... where key = t1.key;
if ( sql%rowcount = 0 )
then
insert into t2 values X;
end if;
end loop;
think "merge"
Instead of coding a join yourself (you would not BELIEVE how many times I see this) think "single query"
Anytime you see a cursor for loop ask yourself "why -- maybe this is just a single DML statement"
Anytime you see someone taking a flat file and reading it row by row (slow by slow) -- doing some lookup -- and then inserting the data -- ask yourself "why isn't that a single sql statement with an external table and maybe a pipelined function"
see
</code>
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:13972936942324 <code>
for example.
Consider what this query does:
with players as
( select 'P'||rownum username
from all_objects
where rownum <= 8),
weeks as
( select rownum week
from all_objects
where rownum <= 7 )
select week,
max(decode(rn,1,username,null)) u1,
max(decode(rn,2,username,null)) u2,
max(decode(rn,3,username,null)) u3,
max(decode(rn,4,username,null)) u4,
max(decode(rn,5,username,null)) u5,
max(decode(rn,6,username,null)) u6,
max(decode(rn,7,username,null)) u7,
max(decode(rn,8,username,null)) u8
from ( select username,
week,
row_number() over (partition by week order by rnd) rn
from ( select username, week, dbms_random.random rnd
from players, weeks
)
)
group by week
/
It is a "program" to randomly assign 8 player to one of two sets of 4 players for 7 weeks.
In short, it creates a golf roster for a tournament.
Think in sets, not procedurally.
Look at my analytic SQL answers -- think in "sets".