Subject Re: Optimizer selects non-optimal plan jstahl80 2007-05-02T12:47:43Z
I prepared a simplified example where the optimizer does not choose
the optimal plan.
The requested result is the same for each query, as far as I can
see, written down in other words only. As far as I can see, the
optimizer is not able to transform a parenthesized left join so that
no natural term in the plan is used for the left table if the set of
cadidate rows of this table is restricted by conditions outside the
perenthesis.

SELECT 1:
select
a.*, c.*
from
t_a a left join t_c c on a.id = c.ta_id
inner join t_b b on a.id = b.member_id
inner join t_a a2 on a2.id = b.owner_id
where
a2.uid = 100

PLAN 1: (non-optimal)
PLAN JOIN (JOIN (JOIN (A NATURAL, C INDEX (PK_TC)), B INDEX
(IX_TB_MEMBER)), A2 INDEX (PK_TA))

SELCET 2:
select
a.*, c.*
from
(t_a a left join t_c c on a.id = c.ta_id)
inner join
(t_b b inner join t_a a2 on a2.id = b.owner_id)
on a.id = b.member_id
where
a2.uid = 100

PLAN 2: (identical to plan 1)
PLAN JOIN (JOIN (JOIN (A NATURAL, C INDEX (PK_TC)), B INDEX
(IX_TB_MEMBER)), A2 INDEX (PK_TA))

SELECT 3:
select
a.*, c.*
from
(t_b b inner join t_a a2 on a2.id = b.owner_id)
inner join
(t_a a left join t_c c on a.id = c.ta_id)
on a.id = b.member_id
where
a2.uid = 100

PLAN 3: (identical to plan 1 and 2)
PLAN JOIN (JOIN (JOIN (A NATURAL, C INDEX (PK_TC)), B INDEX
(IX_TB_MEMBER)), A2 INDEX (PK_TA))

SELECT 4:
select
a.*, c.*
from
(t_b b inner join t_a a2 on a2.id = b.owner_id)
inner join t_a a on a.id = b.member_id
left join t_c c on a.id = c.ta_id
where
a2.uid = 100

PLAN 4: (optimal)
PLAN JOIN (JOIN (A2 INDEX (IX_TA_UID), B INDEX (IX_TB_OWNER), A
INDEX (PK_TA)), C INDEX (PK_TC))

DATABASE:
create table t_a (
id integer not null,
uid integer not null
);

create table t_b (
owner_id integer not null,
member_id integer
);

create table t_c (
ta_id integer not null,
sub_id integer not null
);

insert into t_a (id, uid) values (1, 100);
insert into t_a (id, uid) values (2, 200);
insert into t_a (id, uid) values (3, 300);

commit work;

insert into t_b (owner_id, member_id) values (1, 2);
insert into t_b (owner_id, member_id) values (1, null);
insert into t_b (owner_id, member_id) values (3, 1);

commit work;

insert into t_c (ta_id, sub_id) values (3, 1);
insert into t_c (ta_id, sub_id) values (3, 2);

commit work;

alter table t_a add constraint pk_ta primary key (id);
alter table t_c add constraint pk_tc primary key (ta_id, sub_id);

create index ix_ta_uid on t_a (uid);
create index ix_tb_member on t_b (member_id);
create index ix_tb_owner on t_b (owner_id);