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: '[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 -- Pruner Jan [EMAIL PROTECTED] 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: [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).