Return-Path: Received: from newsfeed.cts.com (newsfeed.cts.com [209.68.248.164]) by naude.co.za (8.11.2/8.11.2) with SMTP id g76LGiN11614 for ; Tue, 6 Aug 2002 17:16:44 -0400 Received: from fatcity.UUCP (uucp@localhost) by newsfeed.cts.com (8.9.3/8.9.3) with UUCP id OAA10282; Tue, 6 Aug 2002 14:16:13 -0700 (PDT) Received: by fatcity.com (26-Feb-2001/v1.0g-b71/bab) via UUCP id 004ACB9A; Tue, 06 Aug 2002 13:58:25 -0800 Message-ID: Date: Tue, 06 Aug 2002 13:58:25 -0800 To: Multiple recipients of list ORACLE-L X-Comment: Oracle RDBMS Community Forum X-Sender: Jared.Still@radisys.com Sender: root@fatcity.com Reply-To: ORACLE-L@fatcity.com Errors-To: ML-ERRORS@fatcity.com From: Jared.Still@radisys.com Subject: RE: Checking the rebuildability of an index Organization: Fat City Network Services, San Diego, California X-ListServer: v1.0g, build 71; ListGuru (c) 1996-2001 Bruce A. Bergman Precedence: bulk Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit I don't do it often, but there are times that it's needed. One I rebuilt recently went from 68 Meg to ~30 meg. This makes a significant reduction in time spent scanning that index, and reduces pressure on the cache buffer. Most may not need rebuilt often, but you should be able to point at data telling you which ones should be rebuilt, which ones shouldn't be rebuilt, and why. There's also the political CYA factor, such as the hot shot consultant that's checking out your systems and wants to know why your indexes haven't been rebuilt, or your buffer hit ratio is 65%, and then proceeds to report to management that you aren't doing your job. You need to have convincing answers for these questions, backed up by data. Jared "Post, Ethan" Sent by: root@fatcity.com 08/06/2002 11:38 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L cc: Subject: RE: Checking the rebuildability of an index If memory serves correct (and it might not) both Tom Kyte and Jonathan Lewis have stated that in most circumstances rebuilding indexes is useless. I think Tom may have said he could count the # of times he had to rebuild an index on one hand (something like that). Thus, I have stopped worrying about this one so much and will only entertain rebuilds if I start to see performance issues. Of course if you have the time and need to look busy keep rebuilding those indexes :) By the way the reason it is useless is not that there is never a performance gain but that most indexes that need to be rebuild quickly degrade again and the period that one experiences a performance gain is minimal. Operating off a poor memory here so hopefully I have not misrepresented anyone. Ethan Post perotdba (AIM), epost1 (Yahoo) -------------------------------------------------------------------- -----Original Message----- Sent: Tuesday, August 06, 2002 1:05 PM To: Multiple recipients of list ORACLE-L Actually, I do the 'validate structure' as well, and neglected to include it in my previous post. Jared > also send the HELP command for other information (like subscribing). > -- __________________________________________________________ Sign-up for your own FREE Personalized E-mail at Mail.com http://www.mail.com/?sr=signup Get 4 DVDs for $.49 cents! plus shipping & processing. Click to join. http://adfarm.mediaplex.com/ad/ck/990-1736-3566-59 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rajesh Dayal INET: rdayal73@techie.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan INET: Ethan.Post@ps.net 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@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@radisys.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@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).