238 SQL Performance Diagnosis on IBM DB2 Universal Database for iSeries
The Index and Statistics Advisor window (Figure 9-7) shows you all the recommended
indexes and statistic columns. If you click Create, you can easily create the suggested
indexes. All key columns are listed in the desired sequence.
Figure 9-7 Index Advisor showing the recommended indexes
9.2 Optimization of your SQL statements
It is not possible to force the optimizer to use a particular index, but you can affect the
optimizer’s decision by coding the SQL select statement in different ways. In the following
section, we show you some ways that you can affect the optimizer.
9.2.1 Avoid using logical files in your select statements
It is a common misunderstanding that, by specifying logical files in SQL statements, the query
optimizer can be forced to choose this index. This is not true. The specified index can be
selected if it meets all the requirements for the best access path. But the query optimizer can
choose any other index or decide even on doing a table scan as well.
If you specify a keyed logical file in an SQL select statement, the optimizer takes only the
column selection, join information, and select/omit clauses, and rewrites the query. For each
of these joined tables, a specific index is determined.
There is another point to avoid specifying logical files in an SQL statement. A select
statement that contains logical files is rerouted and executed by the CQE and does not
benefit from the enhancements of the new SQL Query Engine (SQE). The cost of rerouting
might cause an overhead of up to 10 to 15% in the query optimization time.