Why are the "same" queries optimised differently ?

[PaulR]

We have seen this a lot, but have just experienced the opposite to what
we have always seen previously, so this has prompted me to ask a high
level - why do we get this behaviour?

If we re-write queries in the following pattern (simplified)

select ...
from table1 t1 inner join table2 t2 on (t1.pk = t2.fk)
inner join table3 t3 on (t3.fk = t2.pk)

to

select
(select .. from table3 t3 where t3.fk = t2.pk)
from
table1 t1 inner join table2 t2 on (t1.pk = t2.fk)

It optimises to a plan significantly better (very often).

However, yesterday we saw the opposite (for the first time).

So, my question is simply this (not too much detail req.) -
If/why does the compiler rewrite/optimise these patterns differently
?

Many thanks.

PaulR.

[Juliane via DBMonster.com]

Hi Paul,

When using the explicit JOIN syntax, you force the optimizer to choose that
join order, in your case first t2 and t3 and then t1.
(I just tried it again using empty tables to proove it is still this way in
V8)

If you use the WHERE clause instead, you give the optimizer the freedom of
choosing the join order, it will use statistics to determine which is best
for you.

So how about rewriting using WHERE like

select ... from t1, t2, t3 where t1.pk=t2.fk and t3.fk=t2.pk

That should do it best.

Juliane
--
Message posted via http://www.dbmonster.com

[Serge Rielau]

Juliane via DBMonster.com wrote:

Hi Paul,

When using the explicit JOIN syntax, you force the optimizer to choose that
join order, in your case first t2 and t3 and then t1.
(I just tried it again using empty tables to proove it is still this way in
V8)

I don't this is correct in DB2 V8 for LUW at least.
Without join reordering life in the optimizer would be rather dull ... ;-)
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Juliane via DBMonster.com]

Serge,

That is true for inner joins - I tested with three tables only in the sample
database.

Still, I checked again on the sample database using outer joins:
2 statements returning the same:

a)select c.projname, a.firstnme, a.lastname, b.deptno
from employee a inner join department b on a.empno=b.mgrno left outer join
project c on c.deptno=b.deptno

b)select c.projname, a.firstnme, a.lastname, b.deptno
from department b left outer join project c on c.deptno=b.deptno inner join
employee a on b.mgrno = a.empno

but resulting in different access plans and slightly different costs.

The optimized SQL returns for
a)SELECT Q4.PROJNAME AS "PROJNAME", Q3.$C2 AS "FIRSTNME", Q3.$C1 AS
"LASTNAME",
Q3.$C0 AS "DEPTNO"
FROM
(SELECT Q2.DEPTNO, Q1.LASTNAME, Q1.FIRSTNME
FROM U48JTO.EMPLOYEE AS Q1, U48JTO.DEPARTMENT AS Q2
WHERE (Q1.EMPNO = Q2.MGRNO)) AS Q3 LEFT OUTER JOIN U48JTO.PROJECT AS Q4 ON

(Q4.DEPTNO = Q3.$C0)

b)SELECT Q3.$C2 AS "PROJNAME", Q4.FIRSTNME AS "FIRSTNME", Q4.LASTNAME AS
"LASTNAME", Q3.$C1 AS "DEPTNO"
FROM
(SELECT Q2.MGRNO, Q2.DEPTNO, Q1.PROJNAME
FROM U48JTO.PROJECT AS Q1 RIGHT OUTER JOIN U48JTO.DEPARTMENT AS Q2 ON
(Q1.DEPTNO = Q2.DEPTNO)) AS Q3, U48JTO.EMPLOYEE AS Q4
WHERE (Q3.$C0 = Q4.EMPNO)
Any comments ?

Juliane

Serge Rielau wrote:

Hi Paul,

When using the explicit JOIN syntax, you force the optimizer to choose that
join order, in your case first t2 and t3 and then t1.
(I just tried it again using empty tables to proove it is still this way in
V8)

I don't this is correct in DB2 V8 for LUW at least.
Without join reordering life in the optimizer would be rather dull ... ;-)

--
Message posted via http://www.dbmonster.com

[Serge Rielau]

Juliane via DBMonster.com wrote:

Serge,

That is true for inner joins - I tested with three tables only in the sample
database.

Still, I checked again on the sample database using outer joins:
2 statements returning the same:

a)select c.projname, a.firstnme, a.lastname, b.deptno
from employee a inner join department b on a.empno=b.mgrno left outer join
project c on c.deptno=b.deptno

b)select c.projname, a.firstnme, a.lastname, b.deptno
from department b left outer join project c on c.deptno=b.deptno inner join
employee a on b.mgrno = a.empno

but resulting in different access plans and slightly different costs.

The optimized SQL returns for
a)SELECT Q4.PROJNAME AS "PROJNAME", Q3.$C2 AS "FIRSTNME", Q3.$C1 AS
"LASTNAME",
Q3.$C0 AS "DEPTNO"
FROM
(SELECT Q2.DEPTNO, Q1.LASTNAME, Q1.FIRSTNME
FROM U48JTO.EMPLOYEE AS Q1, U48JTO.DEPARTMENT AS Q2
WHERE (Q1.EMPNO = Q2.MGRNO)) AS Q3 LEFT OUTER JOIN U48JTO.PROJECT AS Q4 ON

(Q4.DEPTNO = Q3.$C0)

b)SELECT Q3.$C2 AS "PROJNAME", Q4.FIRSTNME AS "FIRSTNME", Q4.LASTNAME AS
"LASTNAME", Q3.$C1 AS "DEPTNO"
FROM
(SELECT Q2.MGRNO, Q2.DEPTNO, Q1.PROJNAME
FROM U48JTO.PROJECT AS Q1 RIGHT OUTER JOIN U48JTO.DEPARTMENT AS Q2 ON
(Q1.DEPTNO = Q2.DEPTNO)) AS Q3, U48JTO.EMPLOYEE AS Q4
WHERE (Q3.$C0 = Q4.EMPNO)
Any comments ?

A mixed flavors of joins are harder to permutate.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[PaulR]

So what does the syntax

select
(select .. from table3 t3 where t3.fk = t2.pk)
from
table1 t1 inner join table2 t2 on (t1.pk = t2.fk)

force/assist (if anything) the optimiser to do?

NB. The performance differences are significant, in our usage
scenarios.

Thanks.

[Serge Rielau]

PaulR wrote:

So what does the syntax

select
(select .. from table3 t3 where t3.fk = t2.pk)
from
table1 t1 inner join table2 t2 on (t1.pk = t2.fk)

force/assist (if anything) the optimiser to do?

NB. The performance differences are significant, in our usage
scenarios.

The scalar subselect on table3 has:
"NULL on EMPTY" (like OUTER JOIN) and "ENFORCE MAX CARDINALITY = 1"
semantics.
In theory the optimizer could realize that the foreign key relationship
makes both properties obsolete and reduce the scalar subquery, once
rewritten to an outer join, to a regular inner join.
I'm not sure whether this is done at all and if so under which
circumstances.
If you want to get into this level of detail I'd guess a PMR would be in
order.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab