Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Truncate table, rebuild index?

Re: Truncate table, rebuild index?

From: <>
Date: Mon, 03 Sep 2007 10:45:49 -0700
Message-ID: <>

On Sep 3, 7:41 am, wrote:
> On Sep 3, 8:53 pm, Andrea <> wrote:
> > hi,
> > for some management works i've truncate a table with reuse storage
> > option and next i run "insert" for repopulating table with the same
> > original data. In this case, is useful to rebuild indexes of the table
> > or not?
> > thanks for your response
> > bye
> If the data is re-inserted in no particular order, then the index may
> well end up being rather more 'fresh air' and low density than you'd
> like. A rebuild would recompact the data, so it might be worthwhile.
> But the usual considerations apply: if you compact an index, it will
> immediately begin to 'de-compact' the moment you start doing DML on it
> that requires the insertion of entries into the 'early' part of the
> index. If that is going to happen, is it very sensible to put the
> index into a position where it has to re-claim space it already had
> before the compaction/rebuild? Generally, if DML on an index is
> randomised, indexes will settle themselves down into a 'steady state'
> where rebuilds are not necessarily nor particularly beneficial. It's
> impossible to know from your description whether or not your index
> falls into that category, but chances are it will not.
> But if your question was asking whether an index on a table that is
> truncated and re-inserted into is inevitably going to end up super-
> compact and efficient such that a rebuild would provide no benefit,
> the answer has to be no. Only a re-insertion of the original data in
> key order in non-ASSM tablespace would get anywhere close to that.
> Anything else, and the index will end up in a space usage situation
> which could be "improved" on by a fresh rebuild.

This advice is different than many articles on internet (including yours) which say that rebuilding indexes does not add any value. However, in certain cases like this one, rebuilding indexes can improve performance. Received on Mon Sep 03 2007 - 12:45:49 CDT

Original text of this message