| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Maintaining Stock Availibility : physical table or materialized view ?
Hi,
Thanks for your response.
> I fail to see what it is you think a materialized view will do that
> a column named QTY_ON_HAND won't.
I come from developer perspective, we have number of transactions that could add/subtract the stock, by giving the job to Oracle query via View, we try to avoid the problems/bugs that could exist in our code when updating the stock.
And we are hoping that the STock_Availability view will always show the correct/up to date value, with minimal coding effort.
Is this a wrong reason ?
Thanks,
xtanto
DA Morgan wrote:
> krislioe_at_gmail.com wrote:
> > Hi All,
> > We have typical Order processing application where I should maintain
> > stock availibility => Order is rejected if Stock is not available. The
> > number of orders is 1000 perday, 22 branch, using VPD. 10 users each
> > branch.
> >
> > We are really tempted to use JUST view /materialized view to maintain
> > stock position.
> > (where traditionally we use a phiysical table for this, updated after
> > insert/update/delete of transactions)
> >
> > So the view will be like this :
> >
> > Create View Stock AS
> > Select Prod.Code, Prod.beginning_balance,
> > ( SELECT Sum (Qty) from Purchase Where ProdCode = prod.code
> > ...) AS Purchase,
> > ( SELECT Sum (Qty) from Order Where ProdCode = prod.code ...)
> > AS Order,
> > ........
> > From Products Prod
> >
> > So to get the stock availability for a particular product :
> > Select Beginning_balance + Purchase - Order from Stock Where Code =
> > ...
> >
> > Please give comments, will this work with good performance ?
> > Or has anybody done like this in their application ?
> >
> > Thank you for your help,
> > xtanto
>
>
 |
 |