Oppolzer - Informatik / Blog

Terry's answer covers all the details and of course he knows best
all the bells and whistles of DB2 and the optimizer.

I would like to put it somehow simpler:

which of the two solutions is better depends on the situation.
What another poster said (OUTER JOIN "always" very expensive compared to
NOT EXISTS) is not true. With only two tables and proper indexes, I never
had problems with such OUTER JOINs.

Before DB2 version 4, you had no choice. NOT EXISTS was
the only way to go. Until today this is what I do, if I have poor performing
outer joins (a combination of INNER JOIN ... UNION ALL ... NOT EXISTS;
works almost always without problems).

Kind regards

Bernd



Am 25.09.2014 18:23, schrieb T.P.:
>
> The better performing query will be the one that has the lowest CPU &
> elapsed given your database design.
>
> A correlated EXISTS/NOT EXISTS subquery can perform very well when the
> subquery is appropriately indexed, and also when the outer table and
> subquery are accessed in the same order (if the order that the data
> rows are read from the outer table does not match the subquery index,
> then you can see significant sync I/O). Also - if there are many
> duplicates in the outer and the order from outer to subquery matches,
> then you will benefit from the subquery cache and only execute the
> subquery per distinct outer row.
>
> Joins do not have the caching. But they have more options to sort the
> outer into the sequence of the inner, and/or choose merge scan join or
> sparse index on the inner table if there is inadequate indexing.
>
> Regards
>
> T.P.
>
>
> In Reply to N.Y.:
>
> Hi,
>
> Table test1
>
> K1 K2 K3 DOE
>
> 1 1 1 25.09.20141 1 25.09.2014
> 2 2 2 25.09.20142 2 25.09.2014
> 3 3 3 25.09.20143 3 25.09.2014
> 4 4 4 25.09.20144 4 25.09.2014
> 5 5 5 25.09.2014
>
> Table test2
>
> K1 K2 K3 DOE
>
> 4 4 4 25.09.2014
> 5 5 5 25.09.2014
>
> The Query :
>
> select t1.K1,t1.K2,t1.K3fromtest1 t1 left outerjointest2 t2
> ont1.k1 =t2.k1 andt1.k2 =t2.k2 andt1.k3 =t2.k3 wheret2.DOE isNULL;
>
> retrieves
>
>
> K1 K2 K3
>
> 1 1 1
> 2 2 2
> 3 3 3
>
> The query
>
> Select *fromtest1 t1 wherenotexists( select*fromtest2 t2
> wheret1.k1 =t2.k1 andt1.k2 =t2.k2 andt1.k3 =t2.k3 )
>
> Also retrieves the same.
>
>
> My Question , Which will be better performance query ?
>
> Regards,
>
> N.Y.
>
> -----End Original Message-----