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

From: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 07 Sep 2006 06:33:09 -0700
Message-ID: <1157635985.682956@bubbleator.drizzle.com>

krislioe_at_gmail.com wrote:
> 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

Perhaps a bit melodramatic for me to say this but horribly wrong.

You are as likely to make a mistake in creating your materialized view as you are in an update statement and you should eliminate bugs in your code by testing.

In your product table have a column that holds on-hand inventory. With such a small number of transactions a simple:

SELECT *
FROM products
WHERE product_id = someval
FOR update;

UPDATE products
SET qty_on_hand = qty_on_hand + x
WHERE product_id = someval;

or

UPDATE products
SET qty_on_hand = qty_on_hand - x
WHERE product_id = someval;

should be sufficient.

-- 
Daniel Morgan
University of Washington
Puget Sound Oracle Users Group

Received on Thu Sep 07 2006 - 08:33:09 CDT