Re: Should we rebuild the index?

"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:ti256t7r0kr735_at_beta-news.demon.nl...
>
> <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.
> >
> > --
 

> 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

Unless you need the indexes to enforce constraints during the archive process, you *should* drop the indexes and recreate them, adjusting the allocation as needed.

With 100Gb of data, you should partition the data. If you ever have to query it without partitioning, performance should be terrible.

Good luck,
Chris Weiss Received on Fri Jun 08 2001 - 15:13:49 CDT