Tips for creating temporary indexes from table data

Generally, if a temporary index (from table data) is created, there is room for improvement in the performance of the query. Creating a permanent index that matches the attributes of the temporary index should improve performance of the query. The data attributes of the Temporary Index icon will contain a list of the key columns used to create the temporary index. It also shows the name of the alternate collating sequence table that may have been used. Use the same key columns and alternate collating sequence table when creating a permanent index.

If the temporary index created was a sparse index, you will need to look at the data attributes of the icons preceding the Temporary Index icon. The Table Scan icon, if it exists, contains a list of selection predicate columns used during the table scan. The Key Selection icon, if it exists, contains a list of the selection predicate columns used during key selection. The Key Positioning icon, if it exists, contains a list of the selection predicate columns used during key positioning. To simulate the temporary index that the optimizer created, when creating the permanent index, you will need to specify the selection predicate columns found in any of the 3 icons (table scan, key selection, and key positioning).

For more information, see Database Performance and Query Optimization in the Information Center Link to Information center.