Oppolzer - Informatik / Blog

T.P. wrote:

Bernd,

Sorry for the late reply, just catching up with my emails.

I am not aware of any problems with the non-correlated IN subquery
transformation to join when a unique index exists on the selected column. In
fact I am still seeing this in V6 & V7 (as I would expect).

The APARs listed below refer to the indexable non-column expressions. This
is unrelated to the join transformation, and still appears as a
non-correlated subquery in the plan table, with accesstype N and matchcols >
0 on the outer query. There were also some intial bugs with it, but to my
knowledge these are all resolved now.

If we are both off track with your example, then I would be surprised if
this was not a bug, and something you should report to IBM.

Regards
T.P.


-----Original Message-----
From: DB2 Data Base Discussion List
[mailto:DB2-L@LISTSERV.YLASSOC.COM]
Sent: Thursday, May 09, 2002 8:15 PM
To: DB2-L@LISTSERV.YLASSOC.COM
Subject: Re: DB2 Optimizer and "IN" predicate


I think there must be another qualification or two. This looks like the
change we made in PQ23243 for V5 and also in the base of V6. The subsequent
fixes like PQ29485 and PQ31003 were changes in V5 and V6. With V7, we can
do the rewrite for correlated subqueries as well. Some applications that we
spend lots of time with, such as PeopleSoft, have lots of correlated and
uncorrelated subqueries, and a big regression would not be acceptable.

As you note, we are never perfect, but significant regression from one
version to the next is something we treat as a bug. I still have one on V6
that has taken too long - not optimization.

R.M.


On Thu, 9 May 2002 12:26:30 +0200, Bernd Oppolzer
<Bernd.Oppolzer@T-ONLINE.DE> wrote:

>Hello Terry,
>
>I know of one change, which was NOT better for V6.
>
>In Version 2.3 to 5, DB2 rewrote noncor. subqueries to joins in certain
>circumstances, where it does not with V6.
>
>If you have
>
> select anything
> from table1
> where key1 in
> (select key1
> from table2
> where key2 = 'constant');
>
>then the subquery could be rewritten to a join, if there is a unique index on
>(key1, key2) in table2, because duplicates on the subquery result are not
>possible. As you ask for a constant value for key2, the values for key1 must be
>unique.
>
>This worked in versions 2.3 to 5, but not in V6. Seems to be the 0.1 percent
>:-)
>
>Regards
>
>Bernd