Re: Analyzing indexes

Answers below.

"Yong Huang" <yong321_at_yahoo.com> wrote in message news:b3cb12d6.0303111047.3834a86f_at_posting.google.com...
> Chuck <chuckh_at_softhome.net> wrote in message
news:<Xns933B62023C5ACchuckhsofthomenet_at_130.133.1.4>...
> > I need to determine whether or not a couple of indexes need to be
> > rebuilt. The problem is the indexes are quite large and on a 24x7 high
> > volume database. If I try to run an "analyze validate structure" to
> > gather the data I need to make that decision, it sets a lock on the
> > table for about an hour which I can't afford to do. There is no slow
> > time when I can do this and management has said before they're not going
> > to spring for the partitioning option to break the indexes up into
> > managable pieces. Is there some other way I can get the information
> > needed to determine if an index needs to be rebuilt or not without
> > setting a lock on the table? We are on Oracle 8.1.7.
>
> I don't think there's a way to do what you want in 8.1.7. Only 9i has
> the ONLINE option to allow you to do concurrent DMLs.

But this ONLINE validate doesn't populate index_stats table, which the OP wants, to determine a need for rebuild.

You could restore (part of) your database to a different server and to the check there.

Tanel. Received on Tue Mar 11 2003 - 13:12:51 CST