RE: index rebuild

Hi Mark
 
Do you have index lookup followed by table access by rowid in mind here, if you 
can reduce the number of leaf blocks then you will likely aid any scan 
operations on the index (including range scans not just ffs). Of course you 
will also with most apps just increase the likelyhood of block splits 
occurring... 
 
I believe Tanel suggested on this list a while back that a good test is to run 
two sets of realistic workload against the database, one lot including index 
rebuilds and one not and time the overall elapsed time of the workload, rather 
than the more common test of issue a query, rebuild the index and issue the 
same query with no data changes and voila its all faster. I like this test a 
lot. 
 
Niall Litchfield
Oracle DBA
Audit Commission
+44 117 975 7805 

        -----Original Message-----
        From: oracle-l-bounce@xxxxxxxxxxxxx 
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Bobak, Mark
        Sent: 18 March 2004 20:37
        To: oracle-l@xxxxxxxxxxxxx
        Subject: RE: index rebuild
        
        
        Something to keep in mind when contemplating a rebuild is that (unless 
you do LOTS of index fast full scans) performance will NOT be affected unless 
you can reduce the BLEVEL of the index.
        If you do a rebuild, and the BLEVEL doesn't change, you've probably 
wasted time and resources.  

                -----Original Message-----
                From: Mercadante, Thomas F 
[mailto:thomas.mercadante@xxxxxxxxxxxxxxxxx]
                Sent: Thursday, March 18, 2004 2:47 PM
                To: 'oracle-l@xxxxxxxxxxxxx'
                Subject: RE: index rebuild
                
                
                All,
                 
                I think that the only time I would even consider rebuilding an 
index is if I knew for a fact that a lot of rows had been deleted.  I know the 
term "a lot" is arbitrary, but if I had to quantify it, I would say more than 
30%.  I just don't think that I would see a measurable performance gain for the 
amount of work to be done.  True, a rebuild index command is not a lot of work. 
 But i think we end up playing with these commands and with the objects just 
because we can, rather than because we should.
                 
                just my little old 2 cents.
                 
                Tom Mercadante 
                Oracle Certified Professional 

                        -----Original Message-----
                        From: Jared.Still@xxxxxxxxxxx 
[mailto:Jared.Still@xxxxxxxxxxx]
                        Sent: Thursday, March 18, 2004 1:28 PM
                        To: oracle-l@xxxxxxxxxxxxx
                        Subject: Re: index rebuild
                        
                        

                        consider alter index coalesce 
                        
                        also consider dumping index blocks and determine if you 
have too much space 
                        that is reusable due to a monotonically increasing key, 
and a lot of rows have 
                        been deleted leaving nearly empty and non-reusable 
blocks. 
                        
                        Look on Jonathan Lewis' site, as I believe there is a 
good write up on this. 
                        
                        Jared 
                        
                        
                        
                        
        thump604@xxxxxxxxxxx 
Sent by: oracle-l-bounce@xxxxxxxxxxxxx 

 03/18/2004 08:43 AM 
 Please respond to oracle-l 


        
        To:        oracle-l@xxxxxxxxxxxxx 
        cc:         
        Subject:        index rebuild   



                        There was some recent discusison on here regarding 
rebuilding indices based solely on height.
                        
                        Is this a good criteria to use in determining what 
indices to rebuild:
                        height is greater than 4
                        percentage wasted space on deleted entries compared to 
active
                        entries is greater than 20%
                        percentage of deleted entries compare to active entries 
is
                        greater than 20%
                        
                        Should any other critera be followed and could indices 
be flagged under this criteria and still don't necessarily need to be rebuilt.
                        
                        Thought and opinions please...
                        
                        Thanks
                        --
                        - David 
                        
----------------------------------------------------------------
                        Please see the official ORACLE-L FAQ: 
http://www.orafaq.com
                        
----------------------------------------------------------------
                        To unsubscribe send email to:  
oracle-l-request@xxxxxxxxxxxxx
                        put 'unsubscribe' in the subject line.
                        --
                        Archives are at 
http://www.freelists.org/archives/oracle-l/
                        FAQ is at 
http://www.freelists.org/help/fom-serve/cache/1.html
                        
-----------------------------------------------------------------
                        
                        
                        



**********************************************************************
This email contains information intended for
the addressee only.  It may be confidential
and may be the subject of legal and/or
professional privilege.  Any dissemination,
distribution, copyright or use of this
communication without prior permission of
the sender is strictly prohibited.
**********************************************************************

Other related posts: