RE: truncate a table with many extents

  • From: "Powell, Mark D" <mark.powell@xxxxxxx>
  • To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 6 May 2004 12:10:22 -0400

Jonathan, I agree with you in principal but I checked with my colleague and
he agrees with me that in the past that truncating then dropping a large
table resulted in a significant reduction in the time required to eliminate
a large (few hundred megabyte) table.  I have not done this for a couple of
releases so it may no longer be beneficial.  But then we no longer depend on
dictionary management for most of our tablespaces and the sort tablespace is
now of type temporary so the average level of demand on the ST lock is much
lower for us than it once was.  This should be true for most modern
installations.  If I can find the time and a system with some spare space I
can borrow for a couple hours I will see if I can set up a test and then
post.

-- Mark D Powell --
 

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Jonathan Lewis
Sent: Thursday, May 06, 2004 11:03 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: truncate a table with many extents



Shouldn't make a significant difference.
The undo and redo would only be about
the data dictionary.

There may be some versions of Oracle where 
the different actions produce a different number 
of updates on seg$ or tsq$, though, but that's
just a random thought.

Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Optimising Oracle Seminar - schedule updated May 1st


----- Original Message ----- 
From: "Powell, Mark D" <mark.powell@xxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Thursday, May 06, 2004 3:45 PM
Subject: RE: truncate a table with many extents


If I am not mistaken dropping the table should be much worse than using
truncate because the use of drop would greatly increase the amount of undo
that Oracle has to keep track of.

-- Mark D Powell --


----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: