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: Andrea <>
Date: Mon, 03 Sep 2007 05:39:05 -0700
Message-ID: <>

On 3 Set, 13:41, 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.

ok, but what does it mean for "re-inserted in no particular order"? which order i should have to use for re-insert the rows in this case?
maybe "insert /*+ append */ " ? Received on Mon Sep 03 2007 - 07:39:05 CDT

Original text of this message