Home | Previous Page | Next Page   Tuning Specific Queries and Transactions > Fundamental Query and Transaction Tuning Tasks >

Choose Appropriate Columns for Indexes

Before you create indexes, consider how they might be used. For DSS queries, first evaluate the query performance without any indexes at all. If analysis of the query plan indicates that an index might be useful, follow the guidelines in this section. OLTP transactions, on the other hand, usually benefit from indexes as suggested in this section.

The columns that you choose for an index depend on the data in the columns and the queries that might use the index. Consider how a query or transaction might use the following kinds of indexes:

Indexing Filter Columns in Large Tables

If a column in a large table is often used as a filter in a WHERE clause, consider placing an index on it. The optimizer can use the index to select the required rows and avoid a sequential scan of the entire table. One example is a table that contains a large mailing list. If you find that a postal-code column is often used to filter data, consider creating an index on that column.

This strategy yields a net savings of time only when the cardinality of the column is high; that is, when indexed values are duplicated in only a small fraction of rows. Because nonsequential access through an index takes more disk I/O operations than sequential access, the database server might as well read the table sequentially if a filter expression on the column passes more than a quarter of the rows. Usually, indexing a filter column saves time in the following cases:

Avoiding Columns with Duplicate Keys

When duplicate keys are indexed in a conventional B-tree index, entries that match a given key value are grouped in lists. The database server uses these lists to locate rows that match a requested key value.

If the selectivity of the index column is high, these lists are generally short. But if only a few unique values occur, the lists become long and can even cross multiple leaf pages.

Creating a conventional index on a column that has low selectivity (that is, a small number of distinct values relative to the number of rows) can actually slow performance because of the cost of searching through the duplicate index key values for the rows that satisfy the query.

You can address this problem in both of the following ways:

Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]