Oppolzer - Informatik / Blog

Hi Terry,

I did EXPLAIN on this example. I do it on every release, because I teach the
developers in reading the PLAN_TABLE and analyzing the results. And there showed
up a difference between V5 (where this was transformed to a nested loop join)
and V6 (where this was done in the new V6 way, with N access with the values
coming from the subquery). I think, the elapsed time will be no great
difference, but I believe, the join is still better. Maybe the optimizer checks
the N access first and does not take the join rewrite into account any more.

I just looked at the PLAN_TABLE rows of this example again. The subquery
approach with access type N will sure cost more, because the result of the
subquery is materialized and duplicate eliminated, before the N access can
happen (EXPLAIN shows two Y in the sort fields). This is not true for the join
rewrite.

But I'll check that again.

For installed APARs, I can't tell, because I have nothing to do with DB2
maintenance. I'd have to ask the systems people, but I can't manage it this
week. Sorry.

Regards

Bernd


Am Mon, 13 Mai 2002 schrieben Sie:
> 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