MAINTAINING THE DOMINO AND DB2 ENVIRONMENT
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:
Option Two -- Using two indexes, one with From and one with #modified also avoids accessing rows. The indexes also optimize other queries where the column From or #modified is used in the WHERE clause without the other column.
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.
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:
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.
Related topics