RE: Rebuild Indexes

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Fri, 13 Feb 2004 09:08:01 +0000
Message-Id: <s02c9414.052@bristol21.bristol.ac>

Bear in mind that there are probably no *general* rules - including the one= I am about to give.=20

In general non-bitmap indexes should be rebuilt approximately never. There =
is little or no benefit in the *general* case to rebuilding a standard inde=
x, andd there is nearly always an associated cost while the rebuild happens=

(especially on std edition where you can't do online rebuild).=20

In addition as well as the 'distinctiveness' of an index you should conside=
r carefully the volume of dml on the underlying table as bitmap indexes imp=
ly significant locking when dml happens. I believe this is getting better t=

he higher the version number of the database that you are running. =20

You will get a *slightly* different (and much more thoroughly argued than o= ne can do in an email) view from http://www.dbazine.com/jlewis14.shtml=20

If you have the idea that indexes become unbalanced over time then http://w= ww.dbazine.com/jlewis13.shtml ought to dispell that one for you.=20

Niall Litchfield
Oracle DBA
Audit Commission
+44 117 975 7805=20
> -----Original Message-----
> From: vidya.kalyanaraman_at_oracle.com=20
> Sent: 13 February 2004 08:45
> To: oracle-l_at_freelists.org; vidya.kalyanaraman_at_oracle.com
> Subject: Rebuild Indexes=20

>=20
>=20

> Hi
> It may be a silly thing to ask, but I am stuck right now.=20
> I have been given a task to find out the indexes which need to be=20
> rebuilt. There are around 3000 Indexes. I know I can run=20
> the following=20
> command
> "analyze index <Index> VALIDATE STRUCTURE " =20

>=20

> for a single index and then find the rows from index_state based on=20
> del_lf_rows_len/lf_rows_len > 20%. =20
> Does anyone have a script for dynamically finding out the=20
> indexes that=20
> are the candidates for rebuilding? How do you normally handle=20
> situations like this?

>=20

> TIA
> Vidya

>=20
>=20
>=20
>=20
>=20


> ----------------------------------------------------------------

> 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
> -----------------------------------------------------------------
>=20
>=20

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 Feb 13 2004 - 03:08:01 CST