Oracle-L: Re:Millions of sequence in a database to record the counter

From: <dgoulet_at_vicr.com>
Date: Thu, 18 Jul 2002 09:55:54 -0800
Message-ID: <F001.0049B903.20020718095554@fatcity.com>

Chaos,

Guess you like living up to your name! :)

Anyway, having a sequence for each product would be a nightmare so I would not recommend it. Besides a sequence can get tossed off when you have caching turned on and bounce your DB. That being the case, your sequences will not reflect true values. Now I really don't see a problem with having an application that updates a table several million times a day, it should not be that much of a problem for the database. What you may have to do though is properly design the table and update mechanism. What your looking for is a product_id and the counter value. That looks like a good Index Organized Table to me. Fast access. Second would be to have a function/procedure that does the updates. It should accept the product_id as it's only input and do the update+commit in one action. I've a setup sort a like this that gets around 3M hits per day from the factory floor. Works like a dream with sub second time.

Dick Goulet

____________________Reply Separator____________________
Author: chaos <chaospku_at_163.net>
Date:       7/18/2002 8:58 AM

hi, dbas:

Maybe this is some stupid idear, but this is the best idear i can think , the following is the requirement of our site:

every view to the one page(one product) is recorded, and the count is added by one, doing a commit.
There is 1700K pageview to recorded everyday, thus at lease 1700K commit to the database, every second there is about 40 commits, in peak time, maybe 60/second.
The counter is needed for Data Analyse, so cannot drop it. The developer and the manager want to delay commit, that is , commit after every 100(or 1000) pageview, do a commit. But there is difficulty with multiple middleware that do the delayed commit, and lock contention with one statement to update 100-200 records every second by different middleware servers.

I think use sequence is better to do this work. Every product have a unique product_id, and the old way is:
update products set view_count=view_count+1 where product_id=v_product_id; So there is about 1700K commit of this statement now, and more and more this statement these days, database is burdened too much.

        Now i want to: 
        after every view to some product (one single page), just do a: 
        select seq_product_id.nextval from dual; 
        THis shows the current pageview of the product. 
        But there is about 80k products online, so it means that i have to

create 80k sequence, and with the rapid growth of the products online, there will be more and more products online. Every new product is added to the database, i generate a unique sequence name like 'SEQ_product_ID';

maybe someday 200k products on line, so it means i have to create 200k sequences, and with history products, i also have to keep the old sequence.

This is foolish maybe, but is there any better way to count 80k products with their view_count?

        Please share your opnion. 
        Thanks.

Good luck!

            chaos
            chaospku_at_163.net

zhu chao
DBA of Eachnet.com
86-021-32174588-667

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: chaos
INET: chaospku_at_163.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
INET: dgoulet_at_vicr.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------