| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: truncate a table with many extents
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.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Jonathan Lewis
Sent: Thursday, May 06, 2004 11:03 AM
To: oracle-l_at_freelists.org
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/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
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.
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.htmlput 'unsubscribe' in the subject line.
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org
 |
 |