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

Reply via email to