UNION ALL and OR Predicate optimising vastly differently ?

[PaulR]

Hi,
(re: DB2 LUW v8.1 fp8 optimisation Level2)

Can anyone explain why the following difference have wildly different
plans - this seems very fundamental)

select p.part_number,
sp.supplier_part_number
from jabs.supplier_part sp
inner join jabs.part p on p.oid = sp.part_oid
where sp.supplier_part_number = 'LM339AN'

union all

select p.part_number,
sp.supplier_part_number
from jabs.supplier_part sp
inner join jabs.part p on p.oid = sp.part_oid
where p.part_number = 'LM339AN'

Optimises to a plan of cost = 110 (and runs in 100ms) (plan is index
scans & nested loop joins)

The equivelant (correct me if I'm wrong!)

select p.part_number,
sp.supplier_part_number
from jabs.supplier_part sp
inner join jabs.part p on p.oid = sp.part_oid
where
sp.supplier_part_number = 'LM339AN'
or
p.part_number = 'LM339AN'

optimises to a plan of cost = 340,000 (and takes minutes to run)(plan
is 2x full table access and hash join)

Why the big difference, aren't they equivalent ? - does this look like
an optimiser bug?

[Serge Rielau]

PaulR wrote:

Hi,
(re: DB2 LUW v8.1 fp8 optimisation Level2)

Opt level 2 is below what is recommended for OLTP (we recommend 3).
The lower the opt level the less hard the optimizer will try
Increase the opt level to 3, 5, or 7 and see what it does.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/Fo...Forum2006.html

[jefftyzzer]

Hi, Paul.

Is it that you think that the rewrite facility should be smart enough
to see the equivalence and transform the OR into a UNION, or is it that
you think the access path should (be able to) include an index AND
across tables' indexes?

FWIW, I've seen the same behavior. Actually, it would be more accurate
to say that I'd taken advantage of it, when I was asked to tune a query
with an OR condition on columns from two different tables. The
(well-known) solution was to try converting the OR to UNION, which
worked well in my case.

I guess you're wondering why, if this kind of transformation is so
well-known, it wasn't done in this case?

--Jeff

PaulR wrote:

Hi,
(re: DB2 LUW v8.1 fp8 optimisation Level2)

Can anyone explain why the following difference have wildly different
plans - this seems very fundamental)

select p.part_number,
sp.supplier_part_number
from jabs.supplier_part sp
inner join jabs.part p on p.oid = sp.part_oid
where sp.supplier_part_number = 'LM339AN'

union all

select p.part_number,
sp.supplier_part_number
from jabs.supplier_part sp
inner join jabs.part p on p.oid = sp.part_oid
where p.part_number = 'LM339AN'

Optimises to a plan of cost = 110 (and runs in 100ms) (plan is index
scans & nested loop joins)

The equivelant (correct me if I'm wrong!)

select p.part_number,
sp.supplier_part_number
from jabs.supplier_part sp
inner join jabs.part p on p.oid = sp.part_oid
where
sp.supplier_part_number = 'LM339AN'
or
p.part_number = 'LM339AN'

optimises to a plan of cost = 340,000 (and takes minutes to run)(plan
is 2x full table access and hash join)

Why the big difference, aren't they equivalent ? - does this look like
an optimiser bug?

[PaulR]

Thanks Serge,

I have tried Level3 - it does generate a "different" plan, but ...

cost = 388,000 execution=30+secs

i.e still not as good as the same UNION query where

cost=110 execution=200ms

We have found in our ("OLTP") environment at above Level 2, the
compilation
times are too long to be used. NB. although we are OLTP , we do have
many very complex queries/views - don't ask why ! :-)
Serge Rielau wrote:

PaulR wrote:

Hi,
(re: DB2 LUW v8.1 fp8 optimisation Level2)

Opt level 2 is below what is recommended for OLTP (we recommend 3).
The lower the opt level the less hard the optimizer will try
Increase the opt level to 3, 5, or 7 and see what it does.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/Fo...Forum2006.html

[PaulR]

Hi, thanks for your reply.
jefftyzzer wrote:

Hi, Paul.

Is it that you think that the rewrite facility should be smart enough
to see the equivalence and transform the OR into a UNION, or is it that
you think the access path should (be able to) include an index AND
across tables' indexes?

PRI don't really mind if it rewrites or not, but I would expect
it to find the
PRmuch lower cost plan, it's not a complicated query.

FWIW, I've seen the same behavior. Actually, it would be more accurate
to say that I'd taken advantage of it, when I was asked to tune a query
with an OR condition on columns from two different tables. The
(well-known) solution was to try converting the OR to UNION, which
worked well in my case.

I guess you're wondering why, if this kind of transformation is so
well-known, it wasn't done in this case?

PRYes, I'm just amazed the optimizer hasn't coped well with such
a simple
PRquery. I have raised a call with IBM, I'm hoping there is
fixpak fix.

>
--Jeff

PaulR wrote:

Hi,
(re: DB2 LUW v8.1 fp8 optimisation Level2)

Can anyone explain why the following difference have wildly different
plans - this seems very fundamental)

select p.part_number,
sp.supplier_part_number
from jabs.supplier_part sp
inner join jabs.part p on p.oid = sp.part_oid
where sp.supplier_part_number = 'LM339AN'

union all

select p.part_number,
sp.supplier_part_number
from jabs.supplier_part sp
inner join jabs.part p on p.oid = sp.part_oid
where p.part_number = 'LM339AN'

Optimises to a plan of cost = 110 (and runs in 100ms) (plan is index
scans & nested loop joins)

The equivelant (correct me if I'm wrong!)

select p.part_number,
sp.supplier_part_number
from jabs.supplier_part sp
inner join jabs.part p on p.oid = sp.part_oid
where
sp.supplier_part_number = 'LM339AN'
or
p.part_number = 'LM339AN'

optimises to a plan of cost = 340,000 (and takes minutes to run)(plan
is 2x full table access and hash join)

Why the big difference, aren't they equivalent ? - does this look like
an optimiser bug?