Re: count(*)

Do you really need it?
In case of huge DELETE statement you can always recreate sequence with START WITH <primary_key+1>.

JP

On Friday 20 September 2002 19:04, you wrote:
> but sequences cannot be decremented by PREVVAL too, what about DELETIONS?!
>
> -----Original Message-----
> Sent: Friday, September 20, 2002 8:23 PM
> To: Multiple recipients of list ORACLE-L
>
>
> this is what Oracle invented SEQUENCES for! :)
>
> Tom Mercadante
> Oracle Certified Professional
>
>
> -----Original Message-----
> Sent: Friday, September 20, 2002 10: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

-- 
         Pruner Jan
       jan_at_pruner.cz
     http://jan.pruner.cz/
-----------------------------
Only Robinson Crusoe had all his work done by Friday
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jan Pruner
  INET: jan_at_pruner.cz

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