Create Appropriate Indexes for Transactions

Home | Previous Page | Next Page Tuning Specific Queries and Transactions > Tuning OLTP Applications >

Create Appropriate Indexes for Transactions

The database server can use only one index for an OLTP transaction. For index methods that provide some of the functionality of using multiple indexes, see Using Composite Indexes.

Indexing Columns Used in Group and Sort Statements

When a query or transaction requires ordering or grouping many rows, the database server might select all the rows into a temporary table and sort the table. However, if the ordering columns are indexed, the optimizer can sometimes read the rows in sorted order through the index and avoid a final sort.

Because the keys in an index are in sorted order, the index actually represents the result of sorting the table. When you place an index on the ordering column or columns and the index keys are sorted in the required order, you might replace many sorts with a single sort that occurs when the index is created.

Using Composite Indexes

Composite indexes are commonly used to increase transaction efficiency for active tables in OLTP applications. A composite index can contain up to 16 columns.

You can create a composite index on a table of any type, including a temporary table.

Because a composite index indexes more than one column, it can be tailored to match the SELECT, ORDER BY, and GROUP BY clauses of the transaction or query to improve processing speed.

For example, the optimizer can use an index on the columns a, b, and c, in that order, in the following ways:

For a key-only search, which is a scan that retrieves data from the index without accessing the table
To join column a, columns ab, or columns abc to equivalent columns in another table
To implement ORDER BY or GROUP BY on columns a, ab, or abc, but not on b, c, ac, or bc
To locate a particular row by using equality filters followed by range expressions that use the index keys in order
An index locates a row by specifying the first columns with equality filters and subsequent columns with range expressions (<, <=, >, >=). The following examples of filters use the columns in a composite index:
```
WHERE a=1
WHERE a>=12 AND a<15
WHERE a=1 AND b < 5
WHERE a=1 AND b = 17 AND c >= 40
```
The following examples of filters cannot use the composite index:
```
WHERE b=10
WHERE c=221
WHERE a>=12 AND b=15
```

Execution is most efficient when you create a composite index with the columns in order from most to least distinct. In other words, the first column in a composite index should be the column that returns the highest count of distinct rows when it is queried with the DISTINCT keyword of the SELECT statement.

Tip:

To see the data distribution of columns, use the dbschema utility, which is described in the IBM Informix: Migration Guide.

If your application performs several long queries, each of which contains ORDER BY or GROUP BY clauses, you can sometimes improve performance by adding indexes that produce these orderings without requiring a sort. For example, the following query sorts each column in the ORDER BY clause in a different direction:

SELECT * FROM t1 ORDER BY a, b DESC;

To avoid using temporary tables to sort column a in ascending order and column b in descending order, create a composite index on either (a, b DESC) or (a DESC, b). If your queries sort in both directions, create both indexes. For more information on bidirectional traversal of indexes, refer to the IBM Informix: Guide to SQL Syntax.

On the other hand, it might be less expensive to scan the table and sort the results instead of using the composite index if the number of rows that the query retrieves does not represent a small percentage of the available data.

For large queries at appropriate isolation levels, the database server can use multiple indexes on a table instead of a composite index. Because of the order restrictions of composite indexes, multiple indexes are more flexible than composite indexes. For example, if you have a composite index on columns a, b, and c (in that order), an index on b, and an index on c, the optimizer can use the index on column b and the index on column c to satisfy the following query, but it cannot use the composite index:

SELECT * FROM tab1
WHERE b = 221 AND c = 10;

OLTP transactions, however, are almost always executed at an isolation level that prohibits use of multiple indexes. For this reason, designers of OLTP applications frequently create composite indexes for specific transactions.