RE: truncate a table with many extents

From: Ruth Gramolini <rgramolini_at_tax.state.vt.us>
Date: Fri, 7 May 2004 15:32:20 -0400
Message-ID: <008101c4346a$03cdc050$8459699f@vttaxnet.tax.state.vt.us>

Is the upshot of all this, if you are using 9i (or above) a drop should be faster than a truncate if you don't need to truncate to save the stuff that goes along with the table?

Ruth

  -----Original Message-----
  From: oracle-l-bounce_at_freelists.org
  [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Connor McDonald   Sent: Thursday, May 06, 2004 11:50 PM
  To: oracle-l_at_freelists.org
  Subject: Re: truncate a table with many extents

  I think that's a recent addition though. I remeber an OakTable   discussion about this when we were
  testing 'drop' on lmt's in 8 and 9.0, the tsq$ update occurred   for every extent dropped, and this
  was enhanced in 9.2 to be just once at the end of the drop

  Cheers
  Connor

• Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote: >
  > I think this is one that probably requires testing
  > for every platform and version of Oracle.
  >
  > I've just run a quick test on 9.2.0.4 with 169 extents
  > in an LMT Uniform sized.
  >
  > The most significant difference is that the TRUNCATE
  > updated tsq$ once per extent removed, DROP updated
  > tsq$ once at the end. On the other hand, the DROP
  > command triggered various other (mostly small) actions,
  > including a call to a procedure called aw_drop_proc.
  >
  > The most significant difference, though, was the handling
  > of tsq$, and it made the truncate much more expensive
  > in undo and redo.
  >
  > Regards
  >
  > Jonathan Lewis
  > -----------------------------------------------------------------
  

  ---

  Connor McDonald
  Co-author: "Mastering Oracle PL/SQL - Practical Solutions"   ISBN: 1590592174   web: http://www.oracledba.co.uk
  web: http://www.oaktable.net
  email: connor_mcdonald_at_yahoo.com

  Coming Soon! "Oracle Insight - Tales of the OakTable"

  "GIVE a man a fish and he will eat for a day. But TEACH him how   to fish, and...he will sit in a boat and drink beer all day"

---

---

  Yahoo! Messenger - Communicate instantly..."Ping"   your friends today! Download Messenger Now   http://uk.messenger.yahoo.com/download/index.html

---

  Please see the official ORACLE-L FAQ: http://www.orafaq.com

---

  To unsubscribe send email to: oracle-l-request_at_freelists.org   put 'unsubscribe' in the subject line.   --
  Archives are at http://www.freelists.org/archives/oracle-l/   FAQ is at http://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_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Fri May 07 2004 - 14:30:40 CDT