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).