Index tips

Below are several tips that I've analyzed and implemented on Oracle indexes to increase performance.

1.  Create all primary constraints for all tables.  This creates B-tree indexes.
2.  Review your queries and determine appropriate index selection.  In general, you should have indexes on columns used in majority queries in SELECT, ORDER BY , GROUP BY , UNION, or DISTINCT, etc.
3.  Create indexes on foreign-key columns included in the WHERE clauses when joining tables
4.  Consider bitmap index in data warehouse for low cardinality columns where the values are not updated.
5.  Create indexes on a separate tablespaces from application user data tablespace.
6.  Monitor your indexes, and if a index is not unused, you should drop it to improve the performance of DML statements.
7.  Rebuild indexes are not necessary unless they're corrupted or you need to move the indexes to a separate tablespace
..etc..

A note about locking issue when foreign key columns are not index.  Anytime that you delete in the child table, it places a lock on the parent table until the child table transaction finishes.  To avoid this locking issue, you should have a B-tree index on the foreign key columns.