Bear in mind that there are probably no *general* rules - including the one I am about to give.
In general non-bitmap indexes should be rebuilt approximately never. There is little or no benefit in the *general* case to rebuilding a standard index, andd there is nearly always an associated cost while the rebuild happens (especially on std edition where you can 't do online rebuild).
In addition as well as the 'distinctiveness ' of an index you should consider carefully the volume of dml on the underlying table as bitmap indexes imply significant locking when dml happens. I believe this is getting better the higher the version number of the database that you are running.
You will get a *slightly* different (and much more thoroughly argued than one can do in an email) view from http://www.dbazine.com/jlewis14.shtml
If you have the idea that indexes become unbalanced over time then http://www.dbazine.com/jlewis13.shtml ought to dispell that one for you.
Niall Litchfield
Oracle DBA
Audit Commission
+44 117 975 7805
> -- --Original Message-- --
> From: vidya.kalyanaraman@(protected)
> Sent: 13 February 2004 08:45
> To: oracle-l@(protected); vidya.kalyanaraman@(protected)
> Subject: Rebuild Indexes
>
>
> Hi
> It may be a silly thing to ask, but I am stuck right now.
> I have been given a task to find out the indexes which need to be
> rebuilt. There are around 3000 Indexes. I know I can run
> the following
> command
> "analyze index <Index > VALIDATE STRUCTURE "
>
> for a single index and then find the rows from index_state based on
> del_lf_rows_len/lf_rows_len > 20%.
> Does anyone have a script for dynamically finding out the
> indexes that
> are the candidates for rebuilding? How do you normally handle
> situations like this?
>
> TIA
> Vidya
>
>
>
>
>
> -- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> -- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
> To unsubscribe send email to: oracle-l-request@(protected)
> 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@(protected)
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
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
