RE: Speed up Truncate tables

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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Ron Rogers
  INET: RROGERS_at_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_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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Kevin Lange
  INET: kgel_at_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_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).