Oppolzer - Informatik / Blog

It is true that there are still some differences with how DB2 handles BETWEEN vs
">= AND <=". And it is still on the to-do list to bring these into line - it
just hasn't bubbled up high enough.

Rather than focusing on Filter Factor estimation differences, I would first
target what gets you matching index access.

For example, if you have a simple case of COL BETWEEN ? AND ? or
"COL >= ? AND COL <= ?", then both provide matching index access.

However, ? BETWEEN COL1 AND COL2 is stage 2, whereas "? >= COL1 AND ? <= COL2"
are indexable predicates.

Also, T1.COL BETWEEN T2.COL1 AND T2.COL2 is only indexable if T2 is accessed
before T1, whereas the >=/<= syntax is indexable in either join direction.

Regards
T.


On Thu, 14 Jan 2010 13:41:02 +0000, S.R. wrote:

>As part of our DB2 performance review another interesting question has
>developed. Which is the most efficient way to retrieve rows that are between
>two values.
>Method 1:
>Select * from mytab
> where cola between :hv1 and :hv2
>
>Method 2:
>Select * from mytab
> where cola >= :hv1 > and cola <= :hv2
>
>Two possibilities exist: value of cola available in index or table data will be
>needed to determine value of cola. Is there a difference?
>
>Which is the better and most efficient way to retrieve the data? Are there
>other methods to retrieve these rows?
>
>Thanks to all,
>
>S.R.
>
>
>