Re: Should we rebuild the index?

<u518615722_at_spawnkill.ip-mobilphone.net> wrote in message news:l.992020134.1819610595@[198.138.198.252]...
> We are using 817 on Solaris 2.7.
> We have an archive table. Everyday
> we put our old stuff into it, there
> is never delete, just insert. Over
> time, the table become huge, about
> 100G.
>
> Somebody suggests me to rebuild the indexes
> on this table to boost the performance,
> my thought is, by just inserting, there
> is no fragmentation of index, the B-tree
> just growing, so there is no need to rebuild
> the index, am I right?
>
> Thanks for your help.
>
>
>
>
>
> --
> Sent by dbadba62 from hotmail included in com
> This is a spam protected message. Please answer with reference header.
 

> Posted via http://www.usenet-replayer.com/cgi/content/new

You're not right.
The inserts will not only cause the B-tree to grow, but they will cause bucket-split, and in extreme case an extra level in the B-tree. Generally, an index on a table with many (not to say only) inserts will become less and less efficient, which you could easily monitor by analyze index <index_name> validate structure, followed by select * from index_stats.
I would recommend rebuild the indexes after *every* major load, or better still drop them before you start loading.

Hth,

Sybrand Bakker, Oracle DBA Received on Fri Jun 08 2001 - 13:11:43 CDT