DOMINO AND DB2

Using indexing to improve DB2 performance
An index is a set of one or more keys, each pointing to rows in a table. An index allows efficient access to rows in a table by creating a direct path to the data through pointers. An index optimizes data retrieval without performing a lengthy sequential search.

Indexes optimize two operations in IBM® DB2 Universal Database™ Enterprise Server Edition: table scans and sorts. To understand how indexes optimize table scans, assume you have this SQL statement:


To resolve the query, data is traversed to satisfy the WHERE clause. Without an index, performance degrades linearly as data increases in MyDAV.DAV1. An index that only contains From will help, but the #modified column in the WHERE clause still forces the rows to be read. There are two possible indexing approaches to resolve this problem.
To understand how indexes optimize sorts, consider this example. Assume you have this SQL statement:
A sort will occur unless you use indexing Option One (shown above).

The following SQL statement generates a sort unless you have an index without all four columns in it, in the order in which they appear in the clause ORDER BY.


Not all sorting is expensive. If the WHERE clause is sufficiently selective, for example, returning a few hundred rows, sorts are tolerable and the index overhead is unnecessary. Sorting costs need to be analyzed through tools such as SQL snapshots.

For more information about SQL (DB2 monitor) snapshots, see the topic Generating a database monitor snapshot.

In general, you may want to use an index if you generate an SQL (DB2 monitor) snapshot and the snapshot data shows that you have a slow running query and one or both of the following values is high:

You can use the dynamic SQL snapshot to determine which SQL statements require the most time to execute. Use the DB2 Command Editor to verify whether those statements are using the correct access plan; that is, to determine whether the SQL statement uses a DB2 index versus a table scan.

For information about access plans, see the topic Using SQL Assist to create an SQL statement.

Using an index to automatically order columns

Use indexing to automatically order columns. In an index, column-name identifies a column that is to be part of the index key.

1. Open the DB2 Control Center.

2. Click All Databases, and then locate the initial DB2 database you created. If you did not change the default database name, the name is DOMINO.

3. Open the database, and then click Tables.

4. Select the table whose columns you are indexing, and then right click the mouse.

5. Choose Create Index. The Create Index dialog box appears.


6. Select the columns for which you want to establish a new sort order, and then click the > push button to move the selected column or columns to the Selected columns list box. The order in which the columns appear in the Selected columns list box is the order in which they are stored in the index.

7. (Optional) The Include columns list box is activated when you select the Unique check box. Use the Include columns list box to select additional columns to be included in the index, but not as part of the unique index key.

Note For information about the fields on the Create Index dialog box, see the DB2 Information Center at http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/core/db2idxA.htm.

8. When complete, click OK.

See also