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: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Thu, 06 Nov 2003 19:09:34 -0800
Message-ID: <F001.005D5DE8.20031106190934@fatcity.com>


OK, that's a bitmap index. Bitmap indexes do need to be rebuilt after accumulated maintenance (insert-update-delete) to reclaim space and performance. Not even Tom denies that. What he advises against is the rebuild of b-tree indexes.

At 07:49 PM 11/6/2003, you wrote:
>Hi,
> In some case, rebuild index can help, this is from my work log on my
> Datawarehouse project.
> The effect of rebuiding index:
>SQL> exec show_space('INX_BID_BIDTYPE',USER,'INDEX')
>Free Blocks.............................22
>Total Blocks............................4090
>Total Bytes.............................33505280
>Unused Blocks...........................823
>Unused Bytes............................6742016
>Last Used Ext FileId....................11
>Last Used Ext BlockId...................52851
>Last Used Block.........................547
>
>PL/SQL procedure successfully completed.
>
>
>SQL> select index_name,owner,blevel,index_type from dba_indexes where
>blevel>4;
>
>INDEX_NAME OWNER BLEVEL
>INDEX_TYPE
>------------------------------ ------------------------------ ----------
>---------------------------
>INX_BID_BIDTYPE DAILYLOAD 10
>BITMAP
>INX_TX_CC_STATUS_ID DAILYLOAD 6
>BITMAP
>INX_TX_BD_STATUS_ID DAILYLOAD 5
>BITMAP
>
>SQL> ALTER INDEX INX_BID_BIDTYPE REBUILD;
>
>Index altered.
>
>SQL> exec show_space('INX_BID_BIDTYPE',USER,'INDEX')
>Free Blocks.............................0
>Total Blocks............................5
>Total Bytes.............................40960
>Unused Blocks...........................3
>Unused Bytes............................24576
>Last Used Ext FileId....................15
>Last Used Ext BlockId...................39837
>Last Used Block.........................2
>
>PL/SQL procedure successfully completed.
>
>DAILYSOURCE INX_STD_ST_STATEMENTID
> 122910
>
>149 rows selected.
>
>SQL> CONN SYSTEM/MANAGER_at_STAGEDB
>Connected.
>SQL> ALTER INDEX DAILYSOURCE.INX_STD_ST_STATEMENTID REBUILD;
>
>Index altered.
>
>SQL> select 122910*16/1024 from dual;
>
>
>122910*16/1024
>--------------
> 1920.46875
>
>SQL> select blocks from dba_segments where
>segment_name='INX_STD_ST_STATEMENTID';
>
>
> BLOCKS
>----------
> 54642
>
>SQL> SELECT 54642*16/1024 FROM DUAL;
>
>
>54642*16/1024
>-------------
> 853.78125
>
>SQL> SET PAUSE OFF
>
>----- Original Message -----
>To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
>Sent: Friday, November 07, 2003 6:59 AM
>
>
> > 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: zhu chao
> INET: chao_ping_at_vip.163.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).

Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Wolfgang Breitling
  INET: breitliw_at_centrexcc.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 - 21:09:34 CST

Original text of this message

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