Add Indexes to Tables

When looking for a particular topic in a book, you can either scan the whole book looking for your topic, or you can use the book’s index to find the exact location of the topic directly. An index for a database table is similar in concept to a book index, except that database indexes are used to find specific rows in a table. The downside of indexes is that when a row is added to the table, additional time is required to update the index for the new row.

Generally, you should only create an index on a column when you are retrieving a small number of rows from a table containing many rows. A good rule of thumb is that an index is useful when you expect a single query to retrieve 10 percent or less of the total rows in a table. This means that the candidate column for an index should be used to store a wide range of values. A good candidate for indexing would be a column containing a unique number for each record, while a poor candidate for indexing would be a column that only contains a small range of numeric codes, such as 1, 2, 3, or 4. This consideration applies to all database types, not just numbers. An Oracle database automatically creates an index for the primary key of a table and for columns included in a unique constraint.