When to rebuild index ?

Index is one of intersting objects in database, which always attract DBAs and Developers to fine tune their queries. Because index is just like salt in food. It should be used optimized quantity. Some time index doesnt show actual expected performance. In those cases we need to chaeck whether index need to be rebuild or not.
I went through certain research docs which shows certain criteria for rebuilding index:

The number of deleted leaf nodes - the number of index nodes that have been logically deleted as a result of row deletes. Remember that Oracle leaves "dead" index nodes in the index when rows are deleted. This is done to speed up SQL deletes, since Oracle does not have to rebalance the index tree when rows are deleted.
Index height - the number of levels that are spawned by the index as a result in row inserts. When a large amount of rows are added to a table, Oracle may spawn additional levels of an index to accommodate the new rows. Hence, an Oracle index may have four levels, but only in those areas of the index tree where the massive inserts have occurred. Oracle indexes can support many millions of entries in three levels

Gets per index access - the amount of logical I/O that is required to fetch a row with the index. As you may know, a logical "get" is not necessarily a physical I/O since much of the index may reside in the Oracle buffer cache. Unfortunately, Oracle does not make it easy to capture this information. In Oracle we must issue these commands:
ANALYZE INDEX index_name COMPUTE STATISTICS 
ANALYZE INDEX index_name VALIDATE STRUCTURE 

After you analyze the report above, you may want to consider rebuilding any index where the height is more than three levels, since three levels will support millions of index entries.

As you may know, you can easily rebuild an Oracle index with the command:

ALTER INDEX index_name REBUILD

Seeking to other docs I found there are some myths about it. I am a big fan of richard foote concept on oracle database specially about indexes. he has broken some myth about oracle internals. Following some example is based on his some of documents.

Is deleted leaf blocks are reused? Yes. but depends upon how soon data will be reinserted and while B-Tree will balance the tree will reuse it.

SQL> create table test_empty_block (id number, value varchar2(10));
Table created.
SQL> begin
2 for i in 1..10000 loop
3 insert into test_empty_block values (i, 'Bowie');
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> create index test_empty_block_idx on test_empty_block (id);
Index created.


SQL> delete test_empty_block where id between 1 and 9990;
9990 rows deleted.
SQL> commit;
Commit complete.
SQL> analyze index test_empty_block_idx validate structure;
Index analyzed.
SQL> select lf_blks, del_lf_rows from index_stats;
LF_BLKS DEL_LF_ROWS
---------- -----------
21             9990


Now reinsert a similar volume but after the last current values
SQL> begin
2 for i in 20000..30000 loop
3 insert into test_empty_block values (i, 'Bowie');
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> analyze index test_empty_block_idx validate structure;
Index analyzed.
SQL> select lf_blks, del_lf_rows from index_stats;
LF_BLKS DEL_LF_ROWS
---------- -----------
21             0Note all empty blocks have been reused and deleted rows cleanout.

Following select statement was executed after the 9990 deletions in previous example
SQL> select /*+ index test_empty_blocks */ * from test_empty_blocks
where id between 1 and 100000;
10 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST_EMPTY_BLOCKS'
2 1 INDEX (RANGE SCAN) OF 'TEST_EMPTY_BLOCKS_IDX' (NON-UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
28 consistent gets
0 physical reads
0 redo size
549 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed

• Deleted space is cleaned out by subsequent writes


• Deleted space is cleaned out by delayed block cleanout


• Fully emptied blocks are placed on freelist and recycled (although remain in the index structure)


• Suggestions that deleted space can never be reused are wrong and yet another silly myth

Conditions for Rebuilds
Large free space (generally 50%+), which indexes rarely reach, and
Large selectivity, which most index accesses never reach, and
Response times are adversely affected, which rarely are.
Note requirement of some free space anyways to avoid insert and
subsequent free space issues
Benefit of rebuild based on various dependencies which include:
- Size of index
- Clustering Factor
- Caching characteristics
- Frequency of index accesses
- Selectivity (cardinality) of index accesses
- Range of selectivity (random or specific range)
- Efficiency of dependent SQL
- Fragmentation characteristics (does it effect portion of index frequently used)
- I/O characteristics of index (serve contention or I/O bottlenecks)
- The list goes on and on ....

Myths:

• The vast majority of indexes do not require rebuilding


• Oracle B-tree indexes can become "unbalanced" and need to be rebuilt is a myth


• Deleted space in an index is "deadwood" and over time requires the index to be rebuilt is a myth


• If an index reaches "x" number of levels, it becomes inefficient and requires the index to be rebuilt is a myth


• If an index has a poor clustering factor, the index needs to be rebuilt is a myth


• To improve performance, indexes need to be regularly rebuilt is a myth

I think above discussion will invoke us to test more results in quantitative analysis. I have given enough approach for it.

Test & Enjoy!!