Performance Tuning Tips using Oracle Indexes

1)   Consider indexing keys that are used frequently in WHERE clauses and join tables and with high selectivity. The selectivity of an index is the percentage of rows in a table with the same value for the indexed key. 

a.   Good Selectivity

A table having 1000 records and one of its indexed column has 800 distinct values, then the selectivity of this index is 800 / 1000 = 0.8

b.   Bad Selectivity

lf an index on a table of 1000 records had only 5 distinct values, then the index's selectivity is 5 / 1000 = 0.005 and in this case a query which uses the limitation of such an index will return 1000 / 5 = 200 records for each distinct value. Full table scan is better than an indexed scan in this scenario

2)   Oracle database does not create an index for foreign key constraint automatically. If foreign key column often used in join conditions then create an index on them to enhance the join process

3)   Consider indexing foreign keys for referential integrity constraints in cases where large number of concurrent inserts updates and delete statements access the parent and client tables. Indexing foreign key columns helps avoid full table scans when searching for the matching rows in the child table when DML is performed on parent table. Without an index in the child table a table level lock may occur

4)   When choosing to index a key, consider whether the performance gain for queries is worth the performance loss of inserts, updates and deletes and use of space required storing the index.

5)   Deleting a row from a table in the oracle database results in the deletion of the index entry.  Updates to the key columns result in a logical deletion and insertion of index.

6)   It is recommended that after periods of high DML activity, you verify index statistics and reorganize indexes
Select used, monitoring,
From v$object_usage where index_name =name of the index