Spotlight on Oracle 10.4 - Release Notes

Use Concatenated Indexes

Try not to use two indexes when one would do. If searching for SURNAME and FIRSTNAME, do not unnecessarily create separate indexes for each column. Instead, create a concatenated index on both SURNAME and FIRSTNAME. You can use the leading portion of a concatenated index on its own. If you sometimes query on the SURNAME column without supplying the FIRSTNAME, then SURNAME should come first in the index.

Over-index to avoid a table lookup

Sometimes you can improve SQL execution by over-indexing. Over-indexing involves concatenating columns that appear in the SELECT clause, but not in the WHERE clause to the index.

For example, if you are searching on SURNAME and FIRSTNAME in order to find EMPLOYEE_ID, the concatenated index on SURNAME and FIRSTNAME allows you to quickly locate the row containing the appropriate EMPLOYEE_ID. However, you need to access both the index and the table. If there is an index on SURNAME, FIRSTNAME, and EMPLOYEE_ID, the query can be satisfied using the index alone. This technique can be particularly useful when optimizing joins, since intermediate tables in a join are sometimes queried merely to obtain the join key for the next table.

Consider advanced indexing options

Oracle default B*-tree indexes are flexible and efficient. They are suitable for the majority of situations. However, Oracle offers a number of alternate indexing schemes that can improve performance in specific situations. These include:

• Index clusters allow rows from one or more tables to be located in cluster-key order. Clustering tables can result in a substantial improvement in join performance. However, table scans of individual tables in the cluster can be severely degraded. Index clusters are usually only recommended for tables that are always accessed together. Even then, alternatives (such as de-normalization) should be considered.
• In hash clusters, the key values are translated mathematically to a hash value. Rows are stored in the hash cluster based on this hash value. Locating a row when the hash key is known may require only a single I/O, rather than the two or three I/Os required by an index lookup. However, range scans of the hash key cannot be performed. If the cluster is poorly configured, hash key retrieval can degrade. If the size of the cluster changes, then overflows on the hash keys can occur, or the cluster can become sparsely populated. In this case table scans are less efficient.
• Bit-mapped indexes suit queries on multiple columns that have a few distinct values. They are more compact than a concatenated index. Unlike the concatenated index, they can support queries in which the columns appear in any combination. However, bit-mapped indexes are not suitable for tables that have high modification rates, since locking of bit-mapped indexes occurs at the block, rather than row level. They are also not suitable for columns with large numbers of distinct values.

Make sure your query uses the best index

Novice SQL programmers are often satisfied if the execution plan for their SQL statement uses any index. However, there is sometimes a choice of indexed retrievals, and the Oracle optimizer (especially the older rule-based optimizer) does not always choose the best index. Make sure that the indexes being selected by Oracle are the most appropriate, and use hints to change the index if necessary.

Related Topics

Isolate and Tune Problem SQL

Tune Your Oracle Database