Before rebuilding the indexes, you may need to analyze the indexes and look
at the dictionary (ie dba_indexes,ind ex_stats ) for clustering
factor,blevel(i e 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,includi ng 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@impsat1.c om.arwrote in message
news:YT1Sa.5756 $Uc5.4404@fe06. atl2.webusenet. com...
at the dictionary (ie dba_indexes,ind ex_stats ) for clustering
factor,blevel(i e 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,includi ng 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@impsat1.c om.arwrote in message
news:YT1Sa.5756 $Uc5.4404@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
>
>
>
>
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
>
>
>