db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mike Matrigali <mikem_...@sbcglobal.net>
Subject Re: optimizer and left outer joins
Date Mon, 06 Apr 2009 16:43:37 GMT
No, I just don't know much about this area and am looking at a
query for a customer and just did not understand.  I think it is
working fine.  Without first thinking about it, i saw it was something
like an 7 table and 17 way join and was surprised that the optimizer
actually was only taking 1 second even when I told it to look at all
plans.  But all the joins are left outer joins, so I guess there really
is only one order and only choice is type of join.

I was trying to figure out if hints would help much, but they didn't -
and now I see why.  Thanks for the answer.

/mikem

Rick Hillegas wrote:
> Mike Matrigali wrote:
>> if my multi table query only includes a series of left outer joins, 
>> does  this force the optimizer to only choose that order of tables, 
>> unlike a regular join where it will consider various orders?
>>
>> /mikem
>>
> Hi Mike,
> 
> I haven't looked at the code but that's how it should work. The 
> left-join operator isn't commutative or even associative, so the join 
> order can't be permuted. Jeff was aware of this when he wrote the 
> optimizer. Have you run across a nested left join where the join order 
> looks suspicious?
> 
> Thanks,
> -Rick
> 


Mime
View raw message