SQL Performance Diagnosis on IBM DB2 Universal Database for iSeries

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.

Chapter 9. Tips to prevent further database performance problems 239

We illustrate this with an example. Suppose that we have a base table ORDHDR that contains

all order header information. In addition to a primary key constraint, two logical files are

created.

Example 9-4 show the DDS description of the keyed logical file ORDHDRL1, with the key

fields ORHDTE = Order Date and ORHNBR = Order Number.

Example 9-4 Logical file ORDHDRL1

A R ORDHDRF PFILE(ORDHDR)

*

A K ORHDTE

A K ORHNBR

Example 9-5 shows the DDS description of the second logical file ORDHDRL2, with the key

fields ORHDLY = Delivery Date in descending order, CUSNBR = Customer Number, and

ORHNBR = Order Number.

Example 9-5 Logical file ORDHDRL2

A R ORDHDRF PFILE(ORDHDR)

*

A K ORHDLY DESCEND

A K CUSNBR

A K ORHNBR

Now we want to select all orders with the order date 11 March 2005. In the first case, we

perform the select using the logical file ORDHDRL2, and in the second case, we use the

physical file.