Re: any problem rebuilding indexes used for replication

From: <Jared.Still_at_radisys.com>
Date: Thu, 06 Nov 2003 18:19:25 -0800
Message-ID: <F001.005D5DE3.20031106181925@fatcity.com>

I think Jonathan is correct when he says that index rebuilds are not often needed.

Tom Kyte states that they are never needed, which I don't believe to be correct.

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

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).



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: Jared.Still_at_radisys.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:19:25 CST