Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: index rebuild

RE: index rebuild

From: Niall Litchfield <>
Date: Fri, 19 Mar 2004 09:10:45 -0000
Message-ID: <>

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: [] On Behalf Of Bobak, Mark
	Sent: 18 March 2004 20:37
	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 []
		Sent: Thursday, March 18, 2004 2:47 PM
		To: ''
		Subject: RE: index rebuild
		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: [] Sent: Thursday, March 18, 2004 1:28 PM To: 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

Sent by:

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

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

- David
Please see the official ORACLE-L FAQ:
To unsubscribe send email to: put 'unsubscribe' in the subject line.
Archives are at FAQ is at
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.

Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.
Archives are at
FAQ is at
Received on Fri Mar 19 2004 - 03:07:55 CST

Original text of this message