Query optimization is one of the factors that affect application
performance. Review this section for details about query optimization
considerations that can help you to maximize the performance of database
applications.
Data-access methods
When it compiles an SQL or XQuery statement, the query
optimizer estimates the execution cost of different ways of satisfying
the query.
Joins
A join is the process of combining data from
two or more tables based on some common domain of information. Rows
from one table are paired with rows from another table when information
in the corresponding rows match on the basis of the joining criterion
(the join predicate).
Effects of sorting and grouping on query optimization
When the optimizer chooses an access plan, it considers
the performance impact of sorting data. Sorting occurs when no index
satisfies the requested ordering of fetched rows. Sorting might also
occur when the optimizer determines that a sort is less expensive
than an index scan.
Explain facility
The DB2® explain facility
provides detailed information about the access plan that the optimizer
chooses for an SQL or XQuery statement.
Optimization strategies
The optimization strategies are dependent on the configuration
of the DB2 environment. You
need be aware of this configuration while you are designing performance
improvements.
Access plan optimization
Access plans can be optimized in an attempt to improve
query performance. The degree of improvement depends on the type of
optimization chosen. Optimizing access plans is one of the best ways
to ensure that the query compiler behaves the way you expect and design
it to.