Re: Speed up Truncate tables

A truncate (reuse storage) should not do that - it simply drops the HWM to zero and updates the segment header. However, when a truncate is issued, and dirty blocks in the buffer from that object have to be written to disk before the truncate takes place, so that might be slowing things (a little).

The only other case I can think of is that truncating a table which is actually stored in a cluster does NOT do a truncate, it
does a delete.

Jonathan Lewis

Seminars on getting the best out of Oracle Last few places available for Sept 10th/11th See http://www.jlcomp.demon.co.uk/seminar.html

-----Original Message-----
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: 15 August 2001 17:34

|I had the same problem when truncating a huge table (24 Mill rows).
It
|turned out that the reason my table was taking so long was the amount
of
|extents I had on it. I could look at what was actually happening
during a
|truncate and it had to go and take each individual block and put them
back
|in the available lists.
|
|Well, after changing the settings on the table to make larger extents
(and
|therefore fewer) the truncates on that table went hundreds of times
faster
|(we had real bad settings on that table before).
|
|You might investigate your storage parms and see just how many
extents you
|do have on that table.
|

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).