| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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
 |
 |