How many people here rebuid index regularly? - comp.databases.oracle.server

Message from discussion How many people here rebuid index regularly?
Path: g2news2.google.com!news1.google.com!border1.nntp.dca.giganews.com!nntp.giganews.com!local01.nntp.dca.giganews.com!nntp.bt.com!news.bt.com.POSTED!not-for-mail
NNTP-Posting-Date: Fri, 05 Oct 2007 17:53:22 -0500
From: "Jonathan Lewis" <jonat...@jlcomp.demon.co.uk>
Newsgroups: comp.databases.oracle.server
References: <Y1hNi.7131$DF2.4804@trndny09>   <MPG.216ffe82b29bfb0e98968f@news.dommel.be>   <1191581490.312563.10430@o80g2000hse.googlegroups.com>   <MPG.2170841994c1cc35989691@news.dommel.be> <1191612916.550302.168250@57g2000hsv.googlegroups.com>
Subject: Re: How many people here rebuid index regularly?
Date: Fri, 5 Oct 2007 23:53:16 +0100
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3138
X-RFC2646: Format=Flowed; Original
Message-ID: <DPadnWQ-lar_XZva4p2dnAA@bt.com>
Lines: 31
X-Usenet-Provider: http://www.giganews.com
NNTP-Posting-Host: 86.130.255.131
X-AuthenticatedUsername: NoAuthUser
X-Trace: sv3-ZrB8eMbmLc5jgnzD0GOqEMfh75+LJB4bC29BKaFtjzRiIyBH0bnVN9fHQjMks3LxH7KxpQx4cQH0yua!72Zb6H2A0myguBhvsGdtrCCGUax1XIB9Mx09YJHL48LUPHaQRzmwpF1WQwAhwAzu
X-Complaints-To: abuse@btinternet.com
X-DMCA-Complaints-To: ab...@btinternet.com
X-Abuse-and-DMCA-Info: Please be sure to forward a copy of ALL headers
X-Abuse-and-DMCA-Info: Otherwise we will be unable to process your complaint properly
X-Postfilter: 1.3.36


<fitzjarr...@cox.net> wrote in message 
news:1191612916.550302.168250@57g2000hsv.googlegroups.com...
> I realise this would take time, but have you considered a treedump of
> the indexes before and after the rebuild:
>
> alter session set evnts = 'immediate trace name treedump level <index
> object id>';
>


Test this one on a small index before trying it on a large index.
On some versions of Oracle it does a full dump of all leaf blocks,
rather than one line per block.

Remember, it reads the index one block at a time anyway, so
it can be very slow even when it doesn't to a full dump.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html