Millions of sequence in a database to record the counter for millions of products?

From: chaos <chaospku_at_163.net>
Date: Thu, 18 Jul 2002 08:58:33 -0800
Message-ID: <F001.0049B6F8.20020718085833@fatcity.com>

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: 
	1. every view to the one page(one product) is recorded, and the count is added by one, doing a commit. 
	2. 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. 
	3. 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). Received on Thu Jul 18 2002 - 11:58:33 CDT