When you would make Index and when not.

When you would make Index and when not.
* Create an index if you frequently want to retrieve less than 15% of the rows in a large table.* To improve performance on joins of multiple tables, index columns used for joins.* Small tables do not require indexes.Some columns are strong candidates for indexing. Columns with one or more of the following characteristics are candidates for indexing:* Values are relatively unique in the column.* There is a wide range of values (good for regular indexes).* There is a small range of values (good for bitmap indexes).* The column contains many nulls, but queries often select all rows having a value. In this case, use the following phrase:WHERE COL_X > -9.99 * power(10,125)Using the preceding phrase is preferable to:WHERE COL_X IS NOT NULLThis is because the first uses an index on COL_X (assuming that COL_X is a numeric column). Columns with the following characteristics are less suitable for indexing:* There are many nulls in the column and you do not search on the not null values.The size of a single index entry cannot exceed roughly one-half (minus some overhead) of the available space in the data block.Other Considerations:1. The order of columns in the CREATE INDEX statement can affect query performance. In general, specify the most frequently used columns first.If you create a single index across columns to speed up queries that access, for example, col1, col2, and col3; then queries that access just col1, or that access just col1 and col2, are also speeded up. But a query that accessed just col2, just col3, or just col2 and col3 does not use the index.2. There is a trade-off between the speed of retrieving data from a table and the speed of updating the table. For example, if a table is primarily read-only, having more indexes can be useful; but if a table is heavily updated, having fewer indexes could be preferable.3. Drop Index that are no longer required.4. Using different tablespaces (on different disks) for a table and its index produces better performance than storing the table and index in the same tablespace. Disk contention is reduced.