Re: count(*)

Tom,

The sequences are transaction independant, so the trick with 'select sequence_name,last_number from user_sequences' will only work if there are no rollbacks after insert as well as no use of cache in sequence as you mentioned.
Moreover, what if records get deleted? I guess this may be solved using another trigger on delete selecting another sequence. Than count(*) may be determined as a difference between two sequences. Again there should be no rollbacks.

Rishi, I would also join the point of redesigning of the logic without use of count(*).

Regards,
Alexandre

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Friday, September 20, 2002 2:53 PM

> 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: Alexandre Gorbatchev
  INET: alexandre.gorbatchev_at_avermann.de

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