RE: truncate a table with many extents

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@xxxxxxxxxxxxx
  [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Connor McDonald
  Sent: Thursday, May 06, 2004 11:50 PM
  To: oracle-l@xxxxxxxxxxxxx
  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@xxxxxxxxxxxxxxxxxx> 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@xxxxxxxxx

  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@xxxxxxxxxxxxx
  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@xxxxxxxxxxxxx
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
-----------------------------------------------------------------

Other related posts: