Keys to optimize queries

Use keys in your queries when possible to optimize query performance.

To identify keys, include index definitions on logical tables. When you define index information, front-end tools access the data server to create optimized queries. These queries are based on the SYSIBM.SYSINDEXES and SYSIBM.SYSKEYS information in the metadata catalogs. These index definitions do no actually index the data. Instead, the definitions describe the existing physical indexes on the data.

The entire logical table is scanned in these instances:

• Queries without qualifying WHERE clause information on indexes
• Queries without qualifying information on non-indexed columns

To perform these scans, the query processor must read the entire portion of the database or file that the table defines. The scanning process can result in poor performance on large databases, particularly when an SQL join is performed and an inner table must be scanned multiple times.

When a new application uses existing data, add additional indexes to your data to meet the needs of Classic federation queries. Review your queries to determine whether new indexes can improve performance without impact to another application that needs to use the data.