Hi

>> On a more general issue I know that the optimizer doesn't take into
>> account left outer joins but I wondered why it couldn't treat left
>> outer joins as inner joins when working out the streams (I think

that

>> was the term Arno used) but then ignore any stream that would have
>> a left outer join before an inner join.
>
> With a X LEFT JOIN Y you tell explicit that that Y steam is depended

on the X stream and you'll always need everyting from X.

>

In a simple 2 table join I agree but in a 3 table case like below,
where InvHead.InvHeadRef is a unique value it does a table scan on
IdentityName (100,000 records). The InvHead.IDRef index has a
selectivity of about 2. I know that if I switch the InvHead.IDRef
index off by adding || '' it will then use InvHead first and join into
identityname and account name as I would expect but it would be
better if the optimizer was able to handle such cases. What I find
interesting is that if I change the left outer join to an inner join
it does choose the plan I want. This is why I though if you worked
out all plans assuming all inner joins and then removed the invalid
options that this would return you the correct plan.

select identityname.idref
from identityname
inner join InvHead on identityname.idref = invhead.idref
left join acctname on invhead.invheadref = acctname.invheadref
where invhead.invheadref = 25809

Thanks

Bernard