How to find out which indexes want to rebuild in the database

[Nirmalkumar]

Hi,

Is any datadictionary view or dynamic performance view are there to find out which indexes are want to rebuild in the database.

Help apperciated..

Thanks,
Nirmal.

[Piergiacomo Perini · Accepted solution]

Hi Nirmal (nice to meet u again),

as far as i know doesn't exist a view like you're looking for but
try this "ir.sql" (in attch) as start point.
I have never used neither mainteined neither created;
so use it in test environment.
(again : this seems to work with Oracle Server 7.3.x to 9.x).

note :
Rebuild the index when:
- deleted entries represent 20% or more of the current entries.
- the index depth is more then 4 levels.
Possible candidate for bitmap index:
- when distinctiveness is more than 99%


hth
regards
pg

[spex]

Hello,

If you can afford the time, your best bet is to rebuild every index in your user tablespace(s). I've scheduled the attached script to run every weekend on my production database server (please note that it's not my script, although I may have modified it a bit). It loops through all the indexes in the target tablespace and generates a dynamic script (rebuild_indexes_dynamic.sql) which does an online rebuild of each one.

If rebuilding every index isn't an option for you, see this article for tips on how to identify which indexes should be rebuilt:

http://www.dba-oracle.com/art_index1.htm

PCS

[HGN]

Hi

Please leave feedback you have left feeback only for 3 out of 31 responses, pleas read the forum guidelines, leaving a feedback is a nice way to say thanks for the people who have been helping you.

Rgds

HGN

[Arturo Galbiati]

Hi Nirmal,
you can use the attached script.

HTH,
Art

[Yogeeraj_1]

hi Nirmal,

If you are using Locally managed tablespaces at the backend, this operation should be be arising.

In fact, in normal database operations rebuild of indexes should not be really done.

what version of database are you using?

kind regards
yogeeraj

[Nirmalkumar]

Hi all,


Thanks 2 all for providing the scripts.

yogeraaj- we are using oracle 9i version database.

Thanks,
Nirmal.

[Yogeeraj_1]

hi Again,

If your tablespaces are Locally managed and the indexes have been created on them, then there is nothing to worry about!

To check your tablespaces, run:
select TABLESPACE_NAME, EXTENT_MANAGEMENT
from dba_tablespaces;

hope this helps too!
kind regards
yogeeraj

[John D Wimmer]

Why do you want to rebuild your indexes? You probably don't need to.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2913600659112

[Mahesh Rajendran]

I would second that.
Rebuilding indexes as scheduled job is just a waste of time and resource.
And Switching to LMT does not automatically fix all the problems. LMT with uniform extent size could avoid Extent level fragments, but still there are some holes in block level which you cannot avoid in most cases.