Oracle FAQ Your Portal to the Oracle Knowledge Grid

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

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

From: Howard J. Rogers <>
Date: Fri, 19 Jul 2002 20:16:44 +1000
Message-ID: <ah8ote$l2o$>

Quite why you can't just record the product_id being viewed in a single table and then, when needed, do a count(*) by product_id, I can't imagine. I mean, what you want is the count of the number of times a product has been viewed, not necessarily a count*er*. You can do the one without the other.

Your idea isn't "stupid", but it would be a disaster to implement, has no scalability, practically no manageability, and as my guru Norman has pointed out elsewhere, the use of sequences to actually mean anything is, in any case, quite daft.

HJR "chao_ping" <> wrote in message news: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 - 05:16:44 CDT

Original text of this message