Re: Truncate table, rebuild index?

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 supercompact  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. Received on Mon Sep 03 2007 - 06:41:01 CDT