DB wtf
-
Wierd situation at work, not sure where to post (sorry) - please help?
We have 30+ DBA's, in another state, and our PBX and e-mail systems just went down. Corporate policy precludes using IM (ever), and we are *not* allowed to use our cell phones for corporate business. Someone just discovered the following pattern is used in about a zillion places, and the performance sucks:
Oracle: CURSOR cursor1 return Table1%RowType is select * from Table1 where where-clause-1 for update nowait CURSOR cursor2 return Table1%RowType is select * from Table1 where where-clause-2 for update nowait ... For rec1 in cursor1 loop For rec2 in cursor2 loop update Table1 set ...using data from rec1 where Current of cursor2; update Table1 set someField=variableFromOutsideBothLoops where Current of cursor1; end loop cursor2; end loop cursor1;
I don't know set processing well enough to fix this, but there has to be a (better) way to do it. Any ideas?
Thanks in advance.
-
@snoofle said:
Wierd situation at work, not sure where to post (sorry) - please help?
We have 30+ DBA's, in another state, and our PBX and e-mail systems just went down. Corporate policy precludes using IM (ever), and we are *not* allowed to use our cell phones for corporate business. Someone just discovered the following pattern is used in about a zillion places, and the performance sucks:
Oracle:
CURSOR cursor1 return Table1%RowType is select * from Table1 where where-clause-1 for update nowait
CURSOR cursor2 return Table1%RowType is select * from Table1 where where-clause-2 for update nowait
...
For rec1 in cursor1 loop
For rec2 in cursor2 loop
update Table1 set ...using data from rec1 where Current of cursor2;
update Table1 set someField=variableFromOutsideBothLoops where Current of cursor1;
end loop cursor2;
end loop cursor1;I don't know set processing well enough to fix this, but there has to be a (better) way to do it. Any ideas?
Thanks in advance.
Try to do it all in one or two update statements.
update table1 set x,y,z = (select x,y,z from table1 where ...) where ...;
A few weeks ago, we had a similar problem - lots of insert statements in a for-select-loop. Took many hours - too long to finish in time.
After changing that to a single (admittedly long) insert ... select ...-statement, it now takes less than 2 minutes.
-
*Bingo*
(Thanks :)