1,Create primary key constraint wherever possible which results in index creation by default.
2.Create unique key constraint wherever possible which results in unique index creation by default.
3.Explicitly create indexes on all foreign key columns.
4.Create separate tablespace for all indexes.
5.Create B-tree index until unless there is a solid reason to create a different type of index.
6.Whenever we move the table to different tablespace, indexes becomes invalid. Immediately rebuild all the indexes that are associated with that particular table.
7.If there are any unused indexes just drop them.
SYS>alter index emp_index_lname monitoring usage;
SYS>select object_name,used,monitoring from v$object_usage;
SYS>alter index emp_index_lname no monitoring usage;
