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 <[EMAIL PROTECTED]>
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:
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
[EMAIL PROTECTED]
zhu chao
DBA of Eachnet.com
86-021-32174588-667
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: chaos
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
