RE: index rebuild

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Fri, 19 Mar 2004 09:10:45 -0000
Message-ID: <CFECD6EA683C524BB5FD22C0F1F34ACB011F36CA@bristol43.audit-commission.gov.uk>

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Bobak, Mark
Sent: 18 March 2004 20:37
To: oracle-l_at_freelists.org
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_at_labor.state.ny.us]
Sent: Thursday, March 18, 2004 2:47 PM
To: 'oracle-l_at_freelists.org'
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_at_radisys.com [mailto:Jared.Still_at_radisys.com]
Sent: Thursday, March 18, 2004 1:28 PM
To: oracle-l_at_freelists.org
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_at_comcast.net

Sent by: oracle-l-bounce_at_freelists.org

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

        
        To:        oracle-l_at_freelists.org 
        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_at_freelists.org
			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.

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org 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
-----------------------------------------------------------------

Received on Fri Mar 19 2004 - 03:07:55 CST