Oracle-L: RE: Checking the rebuild ability of an index

From: Jamadagni, Rajendra <Rajendra.Jamadagni_at_espn.com>
Date: Tue, 06 Aug 2002 05:53:55 -0800
Message-ID: <F001.004ABFA4.20020806055355@fatcity.com>

I do 'analyze index ... validate structure' on all indexes and store the output of index_stats table to my own table along with

1. time it takes to analyze
2. Analyze timestamp
3. Complete index rebuild script in a column.
4. I have a column that says if this index should be rebuilt based on some

criteria.

Every Sat 4AM this process runs. At 6AM another process wakes up, and scan through the table to see if any indexes were marked for rebuild, it then proceeds to select the indexes, and 'rebuilds' them, i.e. online. If there are large indexes and the tablespace is fragmented, I usually drop the index, and recreate it based on the criticality of the index on Sun AM when TX rate is low and that's when the index script stored in the column comes handy.

This is not a very sophisticated process, but has been running unattended for last 5 months, thanks to my trusty 'cron' job. The parameters to select an index to rebuild is based from the 'How To Stop ...' paper.

Additionally, I have a set of temporary tables that is used to store temporary data, this process also truncates these tables on a weekly basis. The tables are excluded from analyze index script.

Raj

Rajendra Jamadagni MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

QOTD: Any clod can have facts, but having an opinion is an art!

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jamadagni, Rajendra
  INET: Rajendra.Jamadagni_at_espn.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).



text/plain attachment: InterScan_Disclaimer.txt

Received on Tue Aug 06 2002 - 08:53:55 CDT