From JayMiller@TDWaterhouse.com Wed, 15 Aug 2001 13:11:36 -0700 From: "Miller, Jay" Date: Wed, 15 Aug 2001 13:11:36 -0700 Subject: RE: Speed up Truncate tables Message-ID: MIME-Version: 1.0 Content-Type: text/plain Also,if the problem is caused by a large number of extents my understanding is that it will take just as long to DROP it as to TRUNCATE it. Jay Miller -----Original Message----- Sent: Wednesday, August 15, 2001 4:11 PM To: Multiple recipients of list ORACLE-L THAT depends on what all the dependencies and such are. If you have a lot of referential integrity constraints around the table then it is sometimes a REAL pain to drop and recreate versus keeping the table and the constraints intact. -----Original Message----- Sent: Wednesday, August 15, 2001 2:28 PM To: Multiple recipients of list ORACLE-L If your going to remove all of the records in the table and desire to free up the extents, wouldn't it be faster to drop the table and recreate it? ROR mª¿ªm >>> lisa.koivu@efairfield.com 08/15/01 10:56AM >>> Chuan, You can use the 'REUSE STORAGE' clause of truncate table. That's a heck of a lot faster if you have a whole load of extents allocated to the table. What it does is mark the table as empty and keep all extents. However, I have a feeling that if you really want to release all storage, you'll have to suffer through the complete truncation (without reuse storage). I don't know if there's a way around that. HTH Lisa Koivu Am I an Idiot? (and DBA) Ft. Lauderdale, FL, USA > -----Original Message----- > From: Chuan Zhang [SMTP:chuan@asiaonline.net] > Sent: Wednesday, August 15, 2001 4:10 AM > To: Multiple recipients of list ORACLE-L > Subject: Speed up Truncate tables > > Hi All, > > Is there any way to speed up the truncating a big table with 12 > million > rows? > > Basically, I implemented truncating that big table on Production, but it > affected the performance much, so I had to stop it in the middle of way. > All > the rows were truncated but the HWM was not shrunk at all. I want to do it > again to get the space back. Is there any way to speed up this process? > > Platform: Oracle EE8.0.6 and Solaris 2.7 > > Thanks a lot in advance. > > Chuan > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Chuan Zhang > INET: chuan@asiaonline.net > > 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Rogers INET: RROGERS@galottery.org 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kevin Lange INET: kgel@ppoone.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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: JayMiller@TDWaterhouse.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).