Every once and a while I get asked the question, "How do you when it time to rebuild an index?" I like to use the rule of thumb that if the blevel column in DBA_INDEXES is greater 4, it's time to rebuild the index. Make sure that you analyze the index before checking the blevel columns (analyze index owner.table_name estimate statistics sample 20 percent).
There is some other great index information that can be accessed via the index_stats table, after issuing the "analyze index ... validate structure" command. I have enclosed a script that I created based on some information I found on Metalink. The script basically returns information about what percentage of the index has been deleted, the distinctiveness of the index, and the blevel information. I find this script useful when trying to determine the index that should be used in explain plans.
analyze index &owner.&index_name validate structure ;
select
s.name,s.del_lf_rows*100/decode(s.lf_rows, 0, 1, s.lf_rows) pct_deleted,
(s.lf_rows-s.distinct_keys)*100/ decode(s.lf_rows,0,1,s.lf_rows)
distinctiveness, i.blevel blevel
from
index_stats s,
dba_indexes i
where
i.index_name = s.name ;
Reader Feedback
Sanjay R. writes: IMHO index rebuilds are justified only if there are regular deletes and inserts of more than 20-30% of the records in the table.
This is what I read in a note from an Oracle Expert: I'm a big believer that indexes do not need to be rebuilt except in very particular cases. 99% of index rebuilds are a waste of time. Some people look at the number of deleted entries in the index and rebuild based on that (analyze index index_name validate structure and then query index_stats to see statistics on the index such as deleted entries and such). I say that might be misleading. If 50% of the entries are deleted (leaving "holes" in the index), should we rebuild? Maybe -- maybe not. depends on how the index is used and what data it indexes.
Consider this:
create table emp ( empno int, ename varchar2(25) );
create index emp_idx1 on emp(empno);
create index emp_idx2 on emp(ename)
Now, suppose empno is filled in via a sequence. It's always increasing and we never reuse a value. In our system, we actually delete rows when we fire someone (a real HR system would never do that).
So, emp_idx1 develops "holes" over time that will never be reused. It we find it to be X% "empty" over time, it might be a candidate for a rebuild. We'll never reuse that deleted space (unless we end up deleting all index entries on a block).
But, the index on ename -- that's a little more chaotic. Say we have employees:
Bill Bob Mary
Say we fire Bob, now we have:
Bill
X-Bob (deleted entry)
Mary
Should we rebuild an index like that? Probably NOT. The reason: we are going to hire Hillary (Bill Hillary Mary). Hillary will reuse Bobs slot. There is no reason to get rid of that slot only to have to rebuild it again later. If the data you are indexing has a good probability of reusing a slot like that, rebuilding can actually slow you down over time (it takes time to split a block with empty entries -- the chances we need to split are reduced. If you rebuild, they'll go up; if you unsplit everything, got rid of the space. Now we have to re-split again, every time you rebuild). I find most indexes hit a steady state -- if you rebuild them, they'll go back into that state over time. So rebuilding is somewhat self-defeating in that case.
Make sure to BENCHMARK yourself here. Before rebuilding, capture statistics, performance metrics, IO's, etc against that index. After rebuilding, compare the results. If you got nothing back -- no increased performance, decreased IO, etc -- don't rebuild it again later, it doesn't buy you anything.
About the Author
James Giordano is an Oracle database administrator. He has been working with Oracle for about seven years, and also has experience with UNIX and PeopleSoft/Oracle financials.
For More Information
- What do you think about this tip? E-mail the Edtior at tdichiara@techtarget.com with your feedback.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
- Check out our Ask the Experts feature: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
