判断索引是否要重建【转】

Determine if an index is a good candidate for a rebuild.

 

It is important to regularly examine all the indexes to determine if they have become skewed and might need to be rebuilt.When an index is skewed, parts of an index may be  accessed more frequently than others. As a result, more physical storage utilization and CPU utilization may cause of creating a bottleneck in database performance. Here is a sample procedure on how to identify the such indexes:

 

 

1) Gather statistics on your indexes.

 

SYNTAX--analyze index INDEX_NAME compute statistics;

 

EXAMPLE:

SQL> analyze index RMPB_PROD_CODE compute statistics;

Index analyzed.

 

[for example my index name is 'RMPB_PROD_CODE']

 

2) find out how skewed each index is by running the below given query

SQL> select index_name, blevel,

decode(blevel,0,'OK BLEVEL',1,'OK BLEVEL',2,

'OK BLEVEL',3,'OK BLEVEL',4,'OK BLEVEL','BLEVEL HIGH') OK

from user_indexes where table_name='&TABLE_NAME';

 

 

[for example my table name is 'RMPB_PRODUCT']

 

INDEX_NAME                         BLEVEL OK

------------------------------ ---------- -----------

RMPB_PROD_CODE                          0 OK BLEVEL

RMPB_PROD_NAME                          0 OK BLEVEL

RMPB_PRODUCT_ID                         0 OK BLEVEL

 

 

3) Gather more index statistics using the VALIDATE STRUCTURE option of the ANALYZE command to populate the INDEX_STATS virtual table.

 

SYNTAX--analyze index INDEX_NAME validate structure;

 

EXAMPLE:

SQL> analyze index RMPB_PROD_CODE validate structure;

Index analyzed.

 

[for example my index name is 'RMPB_PROD_CODE']

 

 

 

4) Run the following query to find out PCT_DELETED ratio.

 

SQL> select DEL_LF_ROWS*100/decode(LF_ROWS, 0, 1, LF_ROWS) PCT_DELETED,

(LF_ROWS-DISTINCT_KEYS)*100/ decode(LF_ROWS,0,1,LF_ROWS) DISTINCTIVENESS

from index_stats

where NAME='&index_name';

 

PCT_DELETED DISTINCTIVENESS

----------- ---------------

          0               0

 

[for example my index name is 'RMPB_PROD_CODE']

 

 

The PCT_DELETED column shows the percent of leaf entries (i.e. index entries) that have been deleted and remain unfilled. The more deleted entries exist on an index, the more unbalanced the index becomes. If the PCT_DELETED is 20% or higher, the index is candidate for rebuilding. If you can afford to rebuild indexes more frequently, then do so if the value is higher than 10%. Leaving indexes with high PCT_DELETED without rebuild might cause excessive redo allocation on some systems.

 

The DISTINCTIVENESS column shows how often a value for the column(s) of the index is repeated on average. For example, if a table has 10000 records and 9000 distinct SSN values, the formula would result in (10000-9000) x 100 / 10000 = 10. This shows a good distribution of values. If, however, the table has 10000 records and only 2 distinct SSN values, the formula would result in (10000-2) x 100 /10000 = 99.98. This shows that there are very few distinct values as a percentage of total records in the column. Such columns are not candidates for a rebuild but good candidates for bitmapped indexes.

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22514512/viewspace-616385/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22514512/viewspace-616385/