I performed some testing on this few weeks ago. I couldn't measure the undo blocks due to implicit commits from DDLs. Redo size and redo entries were used instead. Oracle 8.1.7.4 64 bit on Solaris 8. 1. Dictionary managed: Table with 1001 extents 20000 rows 128k extent size Truncate : redo size :756k redo entries : 3031 Drop : redo size :569k redo entries : 2183 2. Locally managed : Same code as above, but this generated 1251 extents. 128k uniform sized LMT Truncate : redo size 1.46MB redo entries : 10,020 Drop : redo size 367K redo entries : 3,804 We decided to use LMTs with drop statements (of course, this table is accessed through a procedure so no grants issue). Surprisingly, # of recursive calls dropped from 15,110 to 307 between truncate and drop statements in case of LMT. It would be nice to see what happens with more # of extents.. Will try that soon. HTH Thanks Riyaj "Re-yas" Shamsudeen Certified Oracle DBA -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Jonathan Lewis Sent: Thursday, May 06, 2004 10: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 ----------------------------------------------------------------- -- Attached file included as plaintext by Ecartis -- -- Desc: Signature The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If the reader of this message is not the intended recipient, you are hereby notified that your access is unauthorized, and any review, dissemination, distribution or copying of this message including any attachments is strictly prohibited. If you are not the intended recipient, please contact the sender and delete the material from any computer. ---------------------------------------------------------------- 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 -----------------------------------------------------------------