Indexes - few notes

DML activity can cause B*Tree indexes to develop sparsely populated blocks.  In an OLTP environment where there is DML activity, some indexes can develop areas that have few entries per block, so this can increase the number of I/Os required to do range scans.  If the number of delete leaf rows exceeds 20% of the size of the leaf rows, you need to rebuild the index or coalesce it (to combine adjacent leaf blocks when two or more adjacent blocks can fit into one block) to improve performance

SQL> analyze index APP_OWNER.ESP_PERMISSION_FK1 VALIDATE STRUCTURE;

Index_stats hols only one row statistics from the last index analyzed.

SQL > select name, del_lf_rows/lf_rows*100 reclaimable_space_pct from index_stats

NAME                                RECLAIMABLE_SPACE_PCT

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

ESP_PERMISSION_FK1                  20

General tips to create indexes to improve SQL performance. 

·         Use B*Tree indexes for join columns and columns frequently appearing in Where clause

·         Use composite indexes for columns frequently used together in where clause.  For large indexes, you should consider to use compressed key indexes

·         Use bitmap indexes for columns with few distinct values and large number of rows.  The index should be for read-only or read-mostly tables

·         Reverse key indexes can reduce block contention (buffer busy waits, enqueue index waits, etc. for concurrent DML and cannot be used for index range scan for queries

·         Compressed key replace the key value with a pointer to the actual key value in the block.   indexes don’t really hurt performance on DML or query.  It work well for large indexes