By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,273 Members | 1,037 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,273 IT Pros & Developers. It's quick & easy.

Filter Factors when using Parameter markers?

P: n/a
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.
Sep 2 '08 #1
Share this Question
Share on Google+
2 Replies


P: n/a
On Sep 2, 5:35*pm, PaulR <paul_red...@uk.ibm.comwrote:
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).

Sep 2 '08 #2

P: n/a
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.
Sep 4 '08 #3

This discussion thread is closed

Replies have been disabled for this discussion.