Oracle-L: Re: Determine if an index needs to be rebuilt... how do I?

From: <Jared.Still_at_radisys.com>
Date: Wed, 13 Mar 2002 14:38:45 -0800
Message-ID: <F001.00428555.20020313143845@fatcity.com>

See the paper 'How to Stop Defragmenting and Start Living'

It has some good methods for determining rebuild candidates.

It's available from several sources, metalink, orapub.com.

Try a search on google.com as well.

Jared

"Fowler, Kenneth R" <kenneth_r_fowler_at_groton.pfizer.com> Sent by: root_at_fatcity.com
03/13/02 12:51 PM
Please respond to ORACLE-L

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc: 
        Subject:        Determine if an index needs to be rebuilt... how do I?

Hi,

I want to automate index rebuilds on some databases I administer. They are
all Oracle 8.1.6 and 8.1.7 on Solaris 2.6 thru 2.8. I want to use "analyze
index...validate structure" and then look at the index_stats view to determine what shape a given index is in.

My question... for people who use this method, what selection criteria do you use on the index_stats view to make the decision to rebuild... or not to
rebuild the particular index???

My aim would be to use such a selection criteria and limit the rebuilds to just a few each night... until at some point they are all done. After that
I would run say once a week just to pick on indexes as they become fragmented and skewed etc etc.

Thanks,
Ken

Clinical and Regulatory Informatics - Groton/New London Coordinator, Business and Technical Services Tel: (860) 732-0026 Fax: (860) 715-8346
Email: mailto:fowlerkr_at_groton.pfizer.com

LEGAL NOTICE
Unless expressly stated otherwise, this message is confidential and may be privileged. It is intended for the addressee(s) only. Access to this E-mail by anyone else is unauthorized. If you are not an addressee, any disclosure or copying of the contents of this E-mail or any action taken (or not taken) in reliance on it is unauthorized and may be unlawful. If you are not an addressee, please inform the sender immediately.
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Fowler, Kenneth R
INET: kenneth_r_fowler_at_groton.pfizer.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
INET: Jared.Still_at_radisys.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------