Tip :- When to rebuid an index ?
Observation 1.
Observe
height in index_stats view after
analyzing the particular index. If it is >= 4
, it is good to rebuild index as it indicate a skewed tree
structure. Although there is always
been an exception to this rule. So observer the height in index_stats
time to time and if height remain unchanged . It may be assumed that It is correct .
Obervation 2.
Observe del_lf_rows and lf_rows in
index_stats view after analyzing the particular index. If
the percentage
(del_lf_rows/lf_rows)*100 > = 20 , It
is good to rebuild index as it indicate unbalanced tree because of high update and delete occurred on it.
ora816 SamSQL :>
analyze index IND_PK validate structure;
ora816 SamSQL :>
select name,height,del_lf_rows,lf_rows,(del_lf_rows/lf_rows) *100 from
index_stats;
NAME HEIGHT
DEL_LF_ROWS LF_ROWS
(DEL_LF_ROWS/LF_ROWS)*100
------------------------------
---------- ----------- ---------- -------------------------
INDX_PK 4 277353 990206
28.0096263
ora816 SamSQL :>
alter index IND_PK rebuild;
Check the index_stats again.
End of Tip
|
Welcome
to send comments or feedback at Wadhwa_s@Hotmail.com |
|
Sameer Wadhwa |
Copyright © Oracle Techniques All Rights Reserved
|