From carmichr@hotmail.com Thu, 16 Aug 2001 18:52:49 -0700 From: "Rachel Carmichael" Date: Thu, 16 Aug 2001 18:52:49 -0700 Subject: Re: Delete followed by Select Count(1) - SLOW Message-ID: MIME-Version: 1.0 Content-Type: text/plain truncate resets the highwater mark.... delete does not, at least not until you have done the count... at that point the highwater mark is reset >From: Walter K >Reply-To: ORACLE-L@fatcity.com >To: Multiple recipients of list ORACLE-L >Subject: Delete followed by Select Count(1) - SLOW >Date: Thu, 16 Aug 2001 13:32:50 -0800 > >I have a user that deleted all of the rows in a table >(i.e. 100,000), waited for it to complete, and then >ran a SELECT COUNT(1) FROM . It took a few >minutes for '0 rows' to be returned to the prompt. The >table has ~60 extents (128k ea.). Granted, the number >of extents is excessive but it's a development >instance and this table is an exception. > >Is Oracle scanning through all of the blocks, since >the space wasn't released, and this is the cause of >the latency? > >The curious thing is that I told this user to use >TRUNCATE instead and we talked about using the >drop/reuse storage clauses. He performed a >TRUNCATE...REUSE STORAGE and the same select and it >was night and day in terms of performance. If the >allocated space isn't being released in this case >also, why is there such a performance difference >between the two? > >-w > >__________________________________________________ >Do You Yahoo!? >Make international calls for as low as $.04/minute with Yahoo! Messenger >http://phonecard.yahoo.com/ >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: Walter K > INET: alden14004@yahoo.com > >Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 >San Diego, California -- Public Internet access / Mailing Lists >-------------------------------------------------------------------- >To REMOVE yourself from this mailing list, send an E-Mail message >to: ListGuru@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). _________________________________________________________________ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: carmichr@hotmail.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru@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).