Rebuilding Indexes

[ksharad]

DBA Gurus,

I am responsibble for maintening Oracle Application [11.5.9] database [9.2.0.3]. As DBA I know when & how should we rebuild index. Can any body share their practices for rebuilding indexes reguraly. How much performance benefit we can get by rebuilding exercise especially when the rows in most tables are not deleted often. Some notes on Metalink say that index building is not really require unless there is bulk row deletes from table. And even that case we might consider reorganising table also.

[nagarjuna]

Monitor your index size and find out the hot indexes. You can write small script to capture index sizing details and insert them into a table and put this script in dbms_job at one day interval. You can query this table to get the trend analysis to find out the hot index.

Just capture the details using the following query..

select owner, segment_name, bytes/1024/1024, extents, segment_type, partition_name, tablespace_name
from dba_segments
/

put all these details into a table and you can query the table later to find out the hot indexes on the basis of no. of extents it has wrapped over a period of time..

[slimdave]

Rebuilding indexes is unlikely to bring you any performance benefits, and may potentially degrade performance until the index has regrown to it's "natural" size.

If you want to recover space due to a high number of deletes, use coalesce, not rebuild.

[abhaysk]

Originally posted by slimdave
and may potentially degrade performance until the index has regrown to it's "natural" size.

Well not really on Queries, but for DML may be..

[OracleDoc]

Dave's right though some indexes like to be fat. The last company I was at we were running Oracle Apps 11.54. They paid to have Tom Kyte to come in and let us DBA's fire off questions at him.

Anyway, he was basically saying the samething that Dave said.
We were re-building indexes every Saturday night. Performance would be slacking up till around Wednesday then it started to get better. We were stumped until Tom mentioned it to us.

[abhaysk]

Originally posted by OracleDoc
We were re-building indexes every Saturday night.

Rebuilding index every week would be a dumb idea.. it should be carefully analyzed whether any index needs rebuild or not..

[OracleDoc]

Originally posted by abhaysk
Rebuilding index every week would be a dumb idea.. it should be carefully analyzed whether any index needs rebuild or not..

Let me refraze that. We had indexes on tables that were getting very heavy DML hence the reason for thd rebuild.

[abhaysk]

Originally posted by OracleDoc
Let me refraze that. We had indexes on tables that were getting very heavy DML hence the reason for thd rebuild.

That will even more worse things if rebuilt once in week..

( fact is immediately after rebuild there will be too much of redo generation due to block splitting )..


Well i would consider rebuild once in a year or at max twice (or when blevel gets more than 3).. its not an attempt to get better performace, but some wasted space.. Yes Queries will get some better perfromcance especially where index FFS and range scans are involved..

Abhay.

[nagarjuna]

That will even more worse things if rebuilt once in week..

( fact is immediately after rebuild there will be too much of redo generation due to block splitting )..


Well i would consider rebuild once in a year or at max twice (or when blevel gets more than 3).. its not an attempt to get better performace, but some wasted space.. Yes Queries will get some better perfromcance especially where index FFS and range scans are involved..

On what basis you are saying one year or six months?? whould you go on rebuilding indexes if they dont take any DMLs at all? and would you wait for one year if the indexed columns are under going too many DMLs?? wont you analyze the growth of your index segments (with respect to extentsor bytes) and plan for rebuild? I will two examples..

We had one bitmap index that has the size of 450mb when we just created it and the table size being 8GB. The index grows to 7GB within a month time. We marked this as hot index and scheduled the rebuild of the index every week end.

On the other hand, we had many indexes thay have never grown. We dont touch these indexes at all.

[OracleDoc]

My point was that we were mis-informed about index rebuild and thought rebuilding indexes once a week was a good thing. It's in the past abhaysk, no need to expand on the subject.