Guidelines When Creating Indexes

• Indexes speed up query performance and slow down DML operations. Always minimize the number of indexes needed on volatile tables
• Place indexes in a separate tablespace, not in a tablespace that has rollback segments, temporary segments, and tables
• There could be significant performance gain for large indexes by avoiding redo generation. Consider using the NOLOGGING clause for creating large indexes
• Because index entries are smaller compared to the rows they index, index blocks tend to have more entries per block. For this reason, INITRANS should generally be higher on indexes than on the corresponding tables