# IC70380: THE OPTIMIZER MIGHT CHOOSE A LESS-THAN-OPTIMAL QUERY ACCESS PLAN AFTER A RUNSTATS IS EXECUTED

## Subscribe

You can track all active APARs for this component.

## Error description

• ```To determine if you are encountering this problem, produce an
EXPLAIN for the query and check for the following conditions:
- there are multiple equality join predicates applied between
the same two tables
- the cardinality of the join is very small (a value less than 1
is a strong indicator)
- the join operation chosen is a NLJOIN when a HSJOIN/MSJOIN is
more likely a better choice

The following is a query with a join between two tables
involving multiple join predicates:
SELECT ...
FROM ... T1, T2, ...
WHERE ... T1.A=T2.A AND T1.B=T2.B AND T1.C=T2.C
```

## Local fix

• ```Collecting column group statistics on the columns involved in
the join will usually solve the problem.  If column group
statistics or an index with the appropriate key is already
available, then you are likely to be encountering this issue.
The following article provides more details on how column group
statistics are used by the optimizer and how they could help in
these scenarios:
http://www.ibm.com/developerworks/data/library/techarticle/dm-06
12kapoor/index.html
```

## Problem summary

• ```Problem Description:
THE OPTIMIZER MIGHT CHOOSE A LESS-THAN-OPTIMAL QUERY ACCESS PLAN
AFTER A RUNSTATS IS EXECUTED

Problem Summary:
To determine if you are encountering this problem, produce an

EXPLAIN for the query and check for the following conditions:
- there are multiple equality join predicates applied between
the same two tables
- the cardinality of the join is very small (a value less than 1
is a strong indicator)
- the join operation chosen is a NLJOIN when a HSJOIN/MSJOIN is
more likely a better choice

The following is a query with a join between two tables
involving multiple join predicates:
SELECT ...
FROM ... T1, T2, ...
WHERE ... T1.A=T2.A AND T1.B=T2.B AND T1.C=T2.C
```

## Problem conclusion

• ```Problem is first fixed in Version 9.7 Fixpack 4
```

## Temporary fix

• ```Collecting column group statistics on the columns involved in

the join will usually solve the problem.  If column group
statistics or an index with the appropriate key is already
available, then you are likely to be encountering this issue.
The following article provides more details on how column group
statistics are used by the optimizer and how they could help in
these scenarios:
http://www.ibm.com/developerworks/data/library/techarticle/dm-06
12kapoor/index.html
```

IC70380

DB2 FOR LUW

DB2FORLUW

970

CLOSED PER

NoPE

NoHIPER

NoSpecatt

2010-08-04

2011-04-29

2011-04-29

DB2 FOR LUW

DB2FORLUW

## Applicable component levels

UP

### Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IC70380

Modified date: 29 April 2011