On Sep 3, 8:35 pm, hjr.pyth..._at_gmail.com wrote:
> 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 re-
> populated 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.- Hide quoted text -
>
> - Show quoted text -
Thanks for clarification.
Received on Mon Sep 03 2007 - 19:41:38 CDT
