Hints for DB2
DB2 on LUW does not allow direct influencing of the
access path, as is allowed by Oracle, DB2 on z/OS and other platforms. There
are several things that can affect the access path chosen.
1. Set Query Optimization Level
This controls the number of rules and methods applied by
the optimizer. Zero (0) is the lowest, 9 is the highest, and 5 is the default.
A higher optimization level will spend more CPU and elapsed time to perform
access path computations.
query optimization level 0; .
where level could be 0,1,2,3,5,7,9
2. Optimize For <n> rows.
This clause is used to influence the optimizer whicfh
respect to the result set size. If a small number or even 1 is specified, DB2
might choose to use an index rather than more costly methods such as a
tablescan or list prefetch. Note that the entire answer can be retrieved, so it
is typically important to limit the rows retrieved to a small number.
Some schools of thought recommend to always use OPTIMIZE
FOR 1 ROW if this clause is specified. This ensures that the access path chosen
will be for a small answer set.
They have Optimize For 1 Row and Optimize For <n>
3. Fetch First <n> Rows Only
In contrast to #2 Optimize For <n> Rows, the Fetch
First puts a hard limit the answer set to only the first <n> rows. This
is very useful when only a few rows are required by the application. Fetch
First should also cause Optimize For to be in effect, but both #2 and #3 can be
4. More Detailed Statistics
DB2 has the capability to collect more detailed index
statistics and column distribution statistics. These statistics can affect the
access path. It is also possible in DB2 LUW to specify the number of frequency
values and quantile values to be collected.
Frequency values show the percentage occurence for the
top <n> values. If a frequently occurring value is specified in a
predicate, such as C1=v, where v occurs frequently enough, DB2 may choose to
avoid using an index and scan the table for all rows. A value that is not in
the <n> (default 10) list of frequent values assumes a unifor
Quantile values help DB2 to determine how many rows might
qualify for a range predicate. The quantile value, in conjunction with HIGH2KEY
and LOW2KEY in from SYSCAT.COLUMNS, allows DB2 to more accurate predict the
number of rows that would be returned from a range.
More detailed index statistics may help DB2 choose a
better access path.
This is an advanced technique that involves setting a DB2
registry variable and a stop/start of DB2. Many customers may not wish to do
this. IBM considers this a technique of last resort. The registry variable and
its implementation is:
Default=NO, Values: YES or NO
registry variable controls where the SELECTIVITY clause can be used in search
conditions in SQL statements.
registry variable is set to YES, the SELECTIVITY clause can be specified for
the following predicates:
basic predicate in which at least one expression contains host variables
LIKE predicate in which the MATCH expression, predicate expression, or escape
expression contains host variables
force application all (to kick off all
The following IBM article is extremely valuable to
understanding DB2 optimization and selectivity: