Rebuilding Indexes

hi ,

There are lot of discussions going on rebuilding indexes..

In simple,the main criteria to rebuild an index is

1.It should not have the BLEVEL more than 4 (you can find these BLEVEL of an index from dba_indexes )

2.Deleted entries should not be more than 20%

here is a query to detect the percentage of deteted rows and Blevel of an index.

select
a.name,a.del_lf_rows*100/decode(a.lf_rows, 0, 1, a.lf_rows) pct_deleted,
(a.lf_rows-a.distinct_keys)*100/ decode(a.lf_rows,0,1,a.lf_rows)
distinctiveness, b.blevel blevel
from
index_stats a,dba_indexes b
where  b.index_name =  a.name;

Make sure u have analyzed the tables before running the above script.

And also i like to share this link  where you can find a healthy discussion about the Index re-organization..

This is an Excellent Discussion going between the DBA gurus..

http://forums.oracle.com/forums/thread.jspa?threadID=840616&tstart=0

Be paitent while reading …hope it helps……..

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s