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 21:15:46 +0200
Message-ID: <>

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

>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
>maybe "insert /*+ append */ " ?

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

Sybrand Bakker
Senior Oracle DBA
Received on Mon Sep 03 2007 - 14:15:46 CDT

Original text of this message