The best update strategy ? ;)
Hi !
Hopes that most of us has the following problem:
Let's say we have two table:
CustomerOrder -- where all customer order is stored and it has field
Status that states where the order is paid or not. The PK
(primary key) for that table is Order_ID.
PayedOrder -- a temporary table that stores all orders that was paid
during last day.It has just one field Order_ID.
And the quest is quite simple -- reflect the payment on the order in
table CustomerOrder.
Restrictions:
1. No ESQL is allowed, all thing must be done in Stored Procedure
Language.
2. The size of CustomerOrder is much bigger than PayedOrder
3. The size of PayedOrder is big enough, that's why this quest could not
be performed in one transaction.
Any suggestions ? :)
Sincerely, Alexander
P.S. I had seen only 2 ways:
1.start the loop on order_id;
begin work;
Update CustomerOrder order set Status=1
where Status=0 and order_id between LoopStep
and LoopStep+LoopAdjust and
exists (select * from PayedOrder a where
a.order_id=CustomerOrder.order_id);
end work;
end loop;
OR
2. start the loop on order_id;
begin work;
FOREACH
select Order_id into v_order_id from PayedOrder where
order_id between LoopStep and LoopStep+LoopAdjust
update CustomerOrder set Status=1 where order_id=vorder_id; END FOREACH;
commit;
end loop;
Each of this ways works badly. May be there is any other solutions ?
--
"People will work eight hours a day for pay, 10 hours a day for a
good boss, and 24 hours a day for a good cause!"
- John C. Maxwell