|
When to Rebuild an Index?
It is important to periodically examine your indexes to determine if they have become skewed and might need to be rebuilt.
When an index is skewed, parts of an index are accessed more frequently than others. As a result, disk contention may occur,creating a bottleneck in performance. Here is a sample procedure on how to identify the such indexes:
1. Gather statistics on your indexes. For large indexes (over one hundred thousand records in the underlying table), use ESTIMATE instead of COMPUTE STATISTICS.
For example:
SQL> analyze index emp_empno_pk compute statistics;
Index analyzed.
2. Run the query below to find out how skewed each index is. This query checks on all indexes that are on emp table.
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='EMP';
|
INDEX_NAME
|
BLEVEL
|
OK
|
|
EMP_EMPNO_PK
|
0
|
OK BLEVEL
|
3. The BLEVEL (or branch level) is part of the B-tree index format and relates
to the number of times Oracle has to narrow its search on the index while
searching for a particular record. In some cases, a separate disk hit is
requested for each BLEVEL. If the BLEVEL were to be more than 4, it is
recommended to rebuild the index.
Note: If you do not analyze the index, the index_check.sql script will
show "BLEVEL HIGH" for such an index.
4. Gather more index statistics using the VALIDATE STRUCTURE option of the
ANALYZE command to populate the INDEX_STATS virtual table.
SQL> analyze index emp_empno_pk validate structure;
Index analyzed.
5. 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,
2 (LF_ROWS-DISTINCT_KEYS)*100/ decode(LF_ROWS,0,1,LF_ROWS) DISTINCTIVENESS
3 from index_stats
4 where NAME='EMP_EMPNO_PK';
|
PCT_DELETED
|
DISTINCTIVENESS
|
|
0
|
0
|
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.
|
