| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: any problem rebuilding indexes used for replication
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
> 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).Received on Thu Nov 06 2003 - 20:49:25 CST
 |
 |