Filter Factors when using Parameter markers?

[PaulR]

Hi,
(DB2 LUW v8.2)

When using parameter markers how does the optimizer evaluate filter
factors?
- and is it able to make use of distribution stats. for parm.
markers?
The reason I ask, is we have heavily skewed data in places and we need
to understand the limitations/estimations the optimizer will use if
and when parameter markers are used.

Many thanks.

PaulR.

[Tonkuma]

On Sep 2, 5:35*pm, PaulR <[e-mail]:

Hi,
(DB2 LUW v8.2)

When using parameter markers how does the optimizer evaluate filter
factors?
* *- and is it able to make use of distribution stats. for parm.
markers?

The reason I ask, is we have heavily skewed data in places and we need
to understand the limitations/estimations *the optimizer will use if
and when parameter markers are used.

Many thanks.

PaulR.

I don't know how optimizer evaluates filter factors for parameter
markers.
But, you can specify SELECTIVITY clause by specifying environment
variable "DB2_SELECTIVITY = Yes"(on DB2 V8 or V9.1. I couldn't find
DB2 environment variables for DB2 V9.5 for LUW).

[Ian]

PaulR wrote:

Hi,
(DB2 LUW v8.2)

When using parameter markers how does the optimizer evaluate filter
factors?

DB2 is able to determine a filter factor based on the cardinality the
column. i.e., filter factor = 1/colcard.

For example,

select * from t1 where c1 = ?

If c1 has 5 unique values, the filter factor = 0.2. But if c1 has
100 unique values, filter factor = 0.01.

- and is it able to make use of distribution stats. for parm.
markers?

The reason I ask, is we have heavily skewed data in places and we need
to understand the limitations/estimations the optimizer will use if
and when parameter markers are used.

Column distribution is not used with parameter markers.

In DB2 9, the REOPT functionality can allow dynamic SQL with parameter
markers (and static SQL) to take advantage of distribution stats.