I think maintaining counts in other table (Naveen's approach) is more of an
application issue. To avoid multiple processes waiting for a lock to update
records in seperate table, you could have each process its dedicated row in
a seperate table with the current count in it. A slight modified version of
Naveen's approach to the problem is described below.
I'm interested in knowing any pitfalls with the following approach, Please
don't hesitate to take a shot at this.
Thanks,
Viral
Lets say, P1, P2, P3, P4 ... Pn processes would insert large number of rows
in large_table.
-- Create a table called rcd_cnter or something like that.
create table rcd_cnter
(
prcss_name varchar2(30) primary key,
recd_count number
);
-- Each Pi would drop and create its own seq. at the
beginining of the process.
-- At the beginning the Pi, it would
use dbms_application_info.set_module to set
the process name i.e. Pi, to identify itself.
This will be later available in v$session.module column to
the trigger on the large_table.
-- Create an after insert trigger on the large_table. The trigger
would query v$seesion.module to identify the module/seq number
to query, and then use that number and v$session.module to insert
or update record in rcd_cntr;
-- At any given time the number of records in the table would be
arrived by following sql
select sum(nvl(recd_cnter,0)) from rcd_cntr;
>Reply-To: [EMAIL PROTECTED]
>To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
>Subject: RE: count(*) Date: Fri, 20 Sep 2002 08:33:37 -0800
>
>Naveen - This approach would probably work fine as long as only a single
>process was running. If multiple processes were inserting rows (likely at
>the scale of millions of rows/hour), this new table would probably be the
>bottleneck. Each process must acquire a lock on this row of this table, so
>the other processes must wait unnecessarily. Been there, done that. Very
>difficult to do what the user asks without degrading performance, which is
>probably why Oracle scales higher that Informix. Okay, cheap shot but worth
>mentioning again.
>
>
>Dennis Williams
>DBA
>Lifetouch, Inc.
>[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
>
>
>-----Original Message-----
>Sent: Friday, September 20, 2002 9:04 AM
>To: Multiple recipients of list ORACLE-L
>
>
>What about having a separate table with a single column and a single row to
>store only the count, and increment and decrement it using a row trigger on
>Insert and deletes?
>
>that way select count(*) will be very fast, the only ovehead will of the
>trigger, which i think should be offset by the performance gained by the
>select.
>
>Regards
>naveen
>
>-----Original Message-----
>Sent: Friday, September 20, 2002 6:24 PM
>To: Multiple recipients of list ORACLE-L
>
>
>Rishi,
>
>Do records get deleted from this table? If not, you could simply add an
>additional column that gets populated by a sequence, add an index on that
>column, and select max() from that column. Even better, simply query
>'select sequence_name,last_number from user_sequences' to get the last
>value
>used. You may need to check whether sequence caching makes a difference
>with this query.
>
>Otherwise, Dennis gave some good advice.
>
>Hope this helps.
>
>Tom Mercadante
>Oracle Certified Professional
>
>
>-----Original Message-----
>Sent: Thursday, September 19, 2002 5:04 PM
>To: Multiple recipients of list ORACLE-L
>
>
>
>Sent: Thursday, September 19, 2002 2:48 PM
>To: '[EMAIL PROTECTED]'
>
>
>Rishi - I've encountered this as well. I think the problem is the fact that
>you are pounding millions of rows into the table. When you ask for a count,
>Oracle won't give you an approximate answer, but insists on giving you a
>precise answer as of the moment you hit return. You are right, your query
>can actually slow performance. No, to my knowledge Oracle doesn't maintain
>a
>record of the number of rows in the table, my guess being that could become
>a performance bottleneck.
> My recommendation would be to ask very precisely what is to be achieved
>with the count. As you noticed, the count will lag reality by quite awhile.
>Perhaps the application could maintain the count. I have quite a few batch
>programs that will display a running counter. If only an approximate count
>is needed, there may be an alternate method, like looking at how many
>segments are used and calculating. Just some thoughts.
>
>Dennis Williams
>DBA
>Lifetouch, Inc.
>[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
>
>
>-----Original Message-----
>[mailto:[EMAIL PROTECTED]]
>Sent: Thursday, September 19, 2002 1:28 PM
>To: Multiple recipients of list ORACLE-L
>
>
>Hi Gurus,
>
>In one of our insert intensive application we are inserting around 3-4
>million rows / hour. Also this app needs to do a count(*) of the tables
>every 10 minutes for verifying some application based logic. This is really
>killing us and it takes a lot of time.
>
>Can you please guide me to a direction ( built in functions or something
>similar).
>
>Actually this app is being ported from Informix. Informix can somehow keep
>a
>trak of the count(*) of a table in its header somewhere.
>
>And yes I have tries count(1) , count(indexed_column) etc.
>
>
>Thanks In Advance.
>
>R.h
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author:
> INET: [EMAIL PROTECTED]
>
>Fat City Network Services -- 858-538-5051 http://www.fatcity.com
>San Diego, California -- Mailing list and web hosting services
>---------------------------------------------------------------------
>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: DENNIS WILLIAMS
> INET: [EMAIL PROTECTED]
>
>Fat City Network Services -- 858-538-5051 http://www.fatcity.com
>San Diego, California -- Mailing list and web hosting services
>---------------------------------------------------------------------
>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: Mercadante, Thomas F
> INET: [EMAIL PROTECTED]
>
>Fat City Network Services -- 858-538-5051 http://www.fatcity.com
>San Diego, California -- Mailing list and web hosting services
>---------------------------------------------------------------------
>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: Naveen Nahata
> INET: [EMAIL PROTECTED]
>
>Fat City Network Services -- 858-538-5051 http://www.fatcity.com
>San Diego, California -- Mailing list and web hosting services
>---------------------------------------------------------------------
>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: DENNIS WILLIAMS
> INET: [EMAIL PROTECTED]
>
>Fat City Network Services -- 858-538-5051 http://www.fatcity.com
>San Diego, California -- Mailing list and web hosting services
>---------------------------------------------------------------------
>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).
_________________________________________________________________
Chat with friends online, try MSN Messenger: http://messenger.msn.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Viral Desai
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
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).
