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/[EMAIL PROTECTED] 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" <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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. > > > > > > [EMAIL PROTECTED] 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 <[EMAIL PROTECTED]> > > Sent by: [EMAIL PROTECTED] > > 11/05/2003 06:14 PM > > Please respond to ORACLE-L > > > > > > To: Multiple recipients of list ORACLE-L > > <[EMAIL PROTECTED]> > > 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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).