Re: Tunning Indexes

Before rebuilding the indexes, you may need to analyze the indexes and look at the dictionary (ie dba_indexes,index_stats ) for clustering factor,blevel(ie binary height) ,leaf nodes,deleted keys etc. Clustering factor indicates how well the data is physically organised within the index. IF the clustering actor approaches the number of leaf_blocks in the index, then you are OK, but if it approaches the number of rows in the table, then it means that more I/O needs to be performed to fetch the index blocks ,unnecessarily and the index becomes a good choice for rebuild. Blevel for most of the indexes,including large ones lies in the range 2-3 which indicates the number of reads to be performed to get to the key value. Also look for the ratio of number of deleted rows to the total number of rows. If you find that this is a considerable percentage and does not find that this is not getting reused by additional inserts,you may need to rebuild . In most cases,Oracle will reuse the space freed up the deletes.

If you do decide to rebuild the index,then you have the option of rebuilding it online (ie doesnt lock simultaneous DML) and NOLOGGING to speedup the index creation. Also you may want to increase session SORT_AREA_SIZE to speed up the sorts during the rebuild.

Hope this helps.
Thiru

"Javier Villegas" <mask_at_impsat1.com.ar> wrote in message news:YT1Sa.5756$Uc5.4404_at_fe06.atl2.webusenet.com...
> HI.
>
> I have tables that have a couple of indexes
>
> The table are growing
>
> I donīt know how can I improve the performance on the indexes (Defrag ,
> rebuild, etc)
>
> Could you tell me how can I do that ?
>
> thanks in advance
>
>
>
Received on Sun Jul 20 2003 - 10:31:11 CDT