Re: Maintaining Stock Availibility : physical table or materialized view ?

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

I fail to see what it is you think a materialized view will do that a column named QTY_ON_HAND won't.

With only 1,000 tx per day a simple update should be invisible to end users.

-- 
Puget Sound Oracle Users Group