Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Millions of sequence in a database to record the counter for millions of products?

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

From: chao_ping <>
Date: 19 Jul 2002 07:17:46 GMT
Message-ID: <3d37bd1a$>

hi, 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.

an oracle fan, 
an oracle beginner

Posted via dBforums
Received on Fri Jul 19 2002 - 02:17:46 CDT

Original text of this message