Re: Query and Update/Insert Large Tables Frequently ...

Indika007 wrote:
> Hi All,
>
> I have the following 2 tables:
>
> Table 1 - Pricing:
>
> This table holds pricing details of all Items (roughly 150,000 items).
> One Item has three types of prices Standard, Promotion, and Discounted.
> Therefore the table contains roughly 150,000 * 3 records. Also this
> table gets updated frequently every day.
>
> Item# Type FromDate ToDate Price
> ========================================
> AAA Standard 01/01/05 31/12/05 1.50
> AAA Promotion 02/12/05 16/12/05 1.40
> AAA Discounted 10/12/05 15/12/05 1.20
> BBB Standard 01/01/05 31/12/05 1.60
> BBB Promotion 02/12/05 24/12/05 1.30
> BBB Discounted 10/12/05 15/12/05 1.50
>
>
> Table 2 - BestPrice:
>
> This table contains best price (lowest price) of all Items for previous
> 21 days including the current date. The POS system is accessing this
> table to get the daily best price for billing customers.
>
> Item# Date BestPrice
> ====================================
> AAA 17/12/05 1.50
> AAA 16/12/05 1.40
> AAA 15/12/05 1.20
> AAA 14/12/05 1.20
> AAA 13/12/05 1.20
> .... ... . ....
> .... .... ....
> BBB 17/12/05 1.30
> BBB 16/12/05 1.30
> BBB 15/12/05 1.30
> BBB 14/12/05 1.30
> BBB 13/12/05 1.30
> .... .... ....
> .... .... ....
>
>
> Problem Statement:
>
> Table 2 (BestPrice) needs to get updated from Table 1 (Pricing) couple
> of times every day with the best price for each Item for the current
> day. Also currently I don't have any data in Table 2 (BestPrice),
> therefore I have to first update this table to have best prices for
> last 20 for all Items too.
>
> What's the most efficient method to perform this job?

Stored procedure using BULK COLLECT and FORALL.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)