Re: Truncate table, rebuild index?

On Mon, 03 Sep 2007 05:39:05 -0700, Andrea <netsecurity_at_tiscali.it> wrote:

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

the only way to get a particular order is insert into ...
select ...
from
order by

-- 
Sybrand Bakker
Senior Oracle DBA