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

Try :
 ANALYZE myTable COMPUTE STATISTICS

It reorganizes the indices (indexes ?) and the performance is much better if you have a table with many updates and a large value spread in the primary key.

Ted

On Thu, 28 Dec 2000 01:00:36 -0000, "António Manuel Correia" <manel.marias_at_netc.pt> wrote:

>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/
>
>

EMail: knijff_at_bigfoot.com Received on Thu Dec 28 2000 - 02:01:40 CST