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" <[EMAIL PROTECTED]> 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: '[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: Alexandre Gorbatchev 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).
