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

Reply via email to