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.

Also, when you perform a hierarchical query (i.e., a query containing a CONNECT BY ) you should add indexes to the columns referenced in the START WITH and CONNECT BY clauses (see Chapter 7 for details on hierarchical queries).

Normally, the DBA is responsible for creating indexes, but as an application developer, you ll be able to provide the DBA with feedback on which columns are good candidates for indexing. This is because you may know more about the application than the DBA. Chapter 10 covers indexes in depth; in that chapter you ll see how to add indexes.