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

From: Mike Krolewski <mkrolewski_at_rii.com>
Date: Wed, 27 Dec 2000 18:35:16 GMT
Message-ID: <92dcp5$lg9$1@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 - 12:35:16 CST