DB2 10.5 for Linux, UNIX, and Windows

Query optimization

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.

The SQL and XQuery compiler process
The SQL and XQuery compiler performs several steps to produce an access plan that can be executed.
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.
Predicate processing for queries
A predicate is an element of a search condition that expresses or implies a comparison operation. Predicates can be grouped into four categories that are determined by how and when the predicate is used in the evaluation process.
Query performance for common SQL statements
A number of performance improvements have been made to improve the speed of many queries. These improvements are automatic. There are no configuration settings or changes to the SQL statements required.
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.
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.
User-maintained materialized query tables
User-maintained materialized query tables (MQTs) are a powerful way to improve response time for complex analytical queries because their data consists of precomputed results from the tables that you specify in the materialized query table definitions.
Explain facility
The DB2 explain facility provides detailed information about the access plan that the optimizer chooses for an SQL or XQuery statement.
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.
Statistical views
The DB2 cost-based optimizer uses an estimate of the number of rows processed by an access plan operator to accurately cost that operator. This cardinality estimate is the single most important input to the optimizer cost model, and its accuracy largely depends upon the statistics that the RUNSTATS command collects from the database.
Catalog statistics
When the query compiler optimizes query plans, its decisions are heavily influenced by statistical information about the size of the database tables, indexes, and statistical views. This information is stored in system catalog tables.
Minimizing RUNSTATS impact
There are several approaches available to improve RUNSTATS performance.

Related concepts:

Writing and tuning queries for optimal performance