Re: how can I check if a index is needed to be rebuild or not?

I found that if we're talking about INSERT performance it's useful to rebuild an index once a while. I have a 70 millin rows table partition with duplicated checking over primary key index and one a new version of that row arrives i've to substitute the old for the new one. In spite that's in very small percentage operation , the index grows beyond expectation. perhaps the stoarge parameters are'nt the best ones ., but i still thing a rebuild doesn't do any harm altough then the stats have to be rebuild.

"Mike Krolewski" <mkrolewski_at_rii.com> wrote in message news:92dcp5$lg9$1_at_nnrp1.deja.com...
> In article <3A4A1726.219EE6A7_at_netscape.net>,
> Cantonese Boy <cantoneseboy_at_netscape.net> wrote:
> > As title, if my index is always has one extent,
> > how can I know when there is a need to rebuild the index?
> > I know that there may be some "holes" inside the index that will
> > cause performance problem, how can I know?
> >
> > Thanks
> >
> > W
> >
>
> Oracle does not generally have to have indexes rebuilt. In 7.3.4, the
> bitmap indexes were unstable if you were doing a lot of insert and/or
> deletes. However, regular indexes are not likely to have such a
> problem. If you want you can 'analyze' the index.
>
> Within the manual:
>
> Oracle collects the following statistics for an index (statistics
> marked with an asterisk are always computed exactly):
> () Depth of the index from its root block to its leaf blocks*
> () Number of leaf blocks
> () Number of distinct index values
> () Average number of leaf blocks per index value
> () Average number of data blocks per index value (for an index on a
> table)
> () Clustering factor (how well ordered the rows are about the indexed
> values)
>
> Index statistics appear in the data dictionary views USER_INDEXES,
> ALL_INDEXES, and DBA_INDEXES.
>
> Your issue may be more a question of the performance of a query. You
> may want to look into the explain plan to determine if the index that
> you think are really being used. Often due to the exact statements and
> constraints within your SQL, Oracle has decided to perform the query a
> different way. Remember to read the sections on how the optimizer reads
> your SQL.
>
> Finally, check run the analyze on the table(s). It will also help
> Oracle make it decisions. Generally, it is good to periodically analyze
> tables especially if the content of the database is changing.
>
>
> --
> Michael Krolewski
> Rosetta Inpharmatics
> mkrolewski_at_rii.com
> Usual disclaimers
>
>
> Sent via Deja.com
> http://www.deja.com/
Received on Wed Dec 27 2000 - 19:00:36 CST