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.
Column group statistics
If your query has more than one join predicate joining
two tables, the DB2 optimizer
calculates how selective each of the predicates is before choosing
a plan for executing the query.
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.
Statistical views
The DB2 cost-based
optimizer uses an estimate of the number of rows - or cardinality
- processed by an access plan operator to accurately cost that operator.
This cardinality estimate is the single most important input to the
optimizer's cost model, and its accuracy largely depends upon
the statistics that the runstats utility collects from the database.
Data compression and performance
Data compression can be used to reduce the amount of data
that must be read from or written to disk, thereby reducing I/O cost.
Inline LOBs improve performance
Some applications make extensive use of large objects (LOBs).
In many cases, these LOBs are not very large-at most, a few kilobytes
in size. The performance of LOB data access can now be improved by
placing such LOB data within the formatted rows on data pages instead
of in the LOB storage object.
Statement concentrator reduces compilation overhead
The statement concentrator modifies dynamic SQL statements
at the database server so that similar, but not identical, SQL statements
can share the same access plan.
Optimization strategies for intra-partition parallelism
The optimizer can choose an access plan to execute a query
in parallel within a single database partition if a degree of parallelism
is specified when the SQL statement is compiled.
Optimization strategies for MDC tables
If you create multidimensional clustering (MDC) tables,
the performance of many queries might improve, because the optimizer
can apply additional optimization strategies. These strategies are
primarily based on the improved efficiency of block indexes, but the
advantage of clustering on more than one dimension also permits faster
data retrieval.
Optimization strategies for partitioned tables
Data partition elimination refers to the database server's
ability to determine, based on query predicates, that only a subset
of the data partitions in a table need to be accessed to answer a
query. Data partition elimination is particularly useful when running
decision support queries against a partitioned table.