Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: any problem rebuilding indexes used for replication

Re: any problem rebuilding indexes used for replication

From: Yong Huang <yong321_at_yahoo.com>
Date: Thu, 06 Nov 2003 18:59:25 -0800
Message-ID: <F001.005D5DE6.20031106185925@fatcity.com>

As I said two weeks ago, somewhere on the Internet people over-trusted authorities. It's different here. I love this place!  

> I'll try to back this up with data in the future.

As a Ph.D in chemistry (sorry to say this), I know how more important repeated experiments done by different people in different labs are than theory. Furthermore, a lab experiment is nothing if a product coming out of a chemical plant says no good.

> So if Tom asks any of you why you don't use automatic space management in
> your LMT's, you can ask him why he doesn't use 'alter index rebuild'. ;)
>
> Jared
>
>
>
>
>
>
> Yong Huang <yong321_at_yahoo.com>
> Sent by: ml-errors_at_fatcity.com
> 11/06/2003 02:59 PM
> Please respond to ORACLE-L
>
>
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> cc:
> Subject: Re: any problem rebuilding indexes used for
> replication
>
>
> renu (and Jared),
>
> The reason I'm very interested in whether there's performance improvement
> is
> that there's a thread on the newsgroup about index rebuild recently. See
>
>

http://groups.google.com/groups?threadm=pan.2003.11.03.08.09.24.330520%40RE-MO-VE.BountifulSolutions.co.uk
>
> Jonathan Lewis says there's almost no need to rebuild indexes, although
> his
> "Practical Oracle8i" lists at least one case you may benefit by
> rebuilding. (I
> don't have the book with me). Asktom.oracle.com has numerous messages
> advising
> against rebuilding indexes. Let's set theory aside for a moment and do the
> experiment. Please post your report of performance change. Thanks.
>
> Yong Huang
>
> --- renu r <renu_p_r_at_yahoo.com> wrote:
> > Jared : I think it is fragmented based on scripts and knowing that there
> have
> > been lot of deletes.
> >
> > One script uses the table index_stats and looks at field del_lf_rows
> which
> > should be less at least in comparison to field lf_rows. I mean less is
> good.
> > more bad. I am not sure about the script but I will post it here if
> someone
> > says so or send it to anyone if they want.
> >
> > I am sure the experts here know about it and can clarify if it is any
> good
> > to look at the index_stats table.
> >
> > One other simple useful script is :
> >
> > SELECT owner, index_name, blevel
> > FROM all_indexes
> > WHERE blevel > 2
> >
> > This can be bacause the size of table is big so it is not definitive. I
> will
> > check the level after rebuild.
> >
> > Benefits expected : Space savings (definitely). performance (hopefully).
> I
> > will let you and Yong know about the benefits if any. If I get some
> help.
> >
> >
> > Jared.Still_at_radisys.com wrote:
> >
> > I'm curious, how have you identified the fragmentation?
> >
> > What benefits do you expect from the rebuild of the indexes?
> >
> > Are you targeting certain indexes that have been identified as
> > benefiting from a rebuild, or just planning to rebuild all indexes?
> >
> > Jared
> >
> >
> >
> >
> > renu r <renu_p_r_at_yahoo.com>
> > Sent by: ml-errors_at_fatcity.com
> > 11/05/2003 06:14 PM
> > Please respond to ORACLE-L
> >
> >
> > To: Multiple recipients of list ORACLE-L
> > <ORACLE-L_at_fatcity.com>
> > cc:
> > Subject: any problem rebuilding indexes used for
> replication
> >
> >
> > Hello,
> > I have to rebuild some primary key indexes due to excessive
> fragmentation.
> > It is rebuild not drop and create. We have multi master replication
> running.
> > Is there any problem to replication if I do that. Has anyone tried it?
> TIA.



Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Yong Huang
  INET: yong321_at_yahoo.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).
Received on Thu Nov 06 2003 - 20:59:25 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US