Re: Truncate table, rebuild index?

On Sep 4, 3:45 am, zigzag..._at_yahoo.com wrote:
> On Sep 3, 7:41 am, hjr.pyth..._at_gmail.com wrote:
>
>
>
> > On Sep 3, 8:53 pm, Andrea <netsecur..._at_tiscali.it> 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.

It's not different at all. I've never said that rebuilding indexes adds no value: try telling that to someone that's just moved their tables, for example. The effect of a rebuild is to compact an index: there's no possible argument about that. There is, however, a different between "effect" and "value". Is compaction of your index something you actually want to happen? If you are about to make the table read-only, then yes, it probably is. But if the table is about to be subject to further reandomised DML, then it almost certainly isn't.

Why is this person truncating and re-inserting data? Do you know the answer to that one? I certainly don't. Therefore I cannot say whether the OP would find rebuilding the index of "value" or not. The point I was making above was simply a physical one: re-inserting data after a truncate will not necessarily cause the index on the table to repopulated  in a completely compact manner, and a rebuild therefore can achieve re-compaction even after a truncate/reinsert. That still leaves it to the OP to decide whether he needs that extra compaction, but if the table is just going to be used for normal DML going forward, then almost certainly he will NOT want that extra bit of compaction.

Your last sentence is just devoid of real content. In certain cases, rebuilding indexes can improve performance and in certain cases, and this might be one of them, it can worsen performance. It's like anything in Oracle: you can't make meaningful sweeping statements. Received on Mon Sep 03 2007 - 19:35:51 CDT