RE: count(*)

From: Viral Desai <viral303_at_hotmail.com>
Date: Fri, 20 Sep 2002 11:01:51 -0800
Message-ID: <F001.004D5690.20020920110151@fatcity.com>

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: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>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.
>dwilliams_at_lifetouch.com <mailto:dwilliams_at_lifetouch.com>
>
>
>-----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: 'ORACLE-L_at_fatcity.com'
>
>
>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.
>dwilliams_at_lifetouch.com <mailto:dwilliams_at_lifetouch.com>
>
>
>-----Original Message-----
>[mailto:Rishi.Jain_at_VerizonWireless.com]
>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: Rishi.Jain_at_VerizonWireless.com
>
>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: ListGuru_at_fatcity.com (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: DWILLIAMS_at_LIFETOUCH.COM
>
>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: ListGuru_at_fatcity.com (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: NDATFM_at_labor.state.ny.us
>
>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: ListGuru_at_fatcity.com (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: naveen_nahata_at_mindtree.com
>
>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: ListGuru_at_fatcity.com (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: DWILLIAMS_at_LIFETOUCH.COM
>
>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: ListGuru_at_fatcity.com (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: viral303_at_hotmail.com

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: ListGuru_at_fatcity.com (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).

Received on Fri Sep 20 2002 - 14:01:51 CDT