Tom Conlon wrote:

>
> '...In joining two or more streams together, it is often crucial to
> determine which stream should be retrieved first. In general, the
> stream which is the most expensive to retrieve should be retrieved
> first. If you think of a join as a set of nested loops, it makes sense
> that the innermost loop will be executed the most times. Similarly,
> the last stream in the join list will be fetched the most times, so it
> had better be the cheapest to retrieve.'
>
> Is this still the case (FB 1.5 & 2.0)?

Yes. That's what the optimizer attempts to do. However, it
does not do the transformations necessary to convert a subquery
to a join - transformations that become quite complex when the
subquery can return a null set.

>
>> the last stream in the join list will be fetched the most times
>
> Can someone confirm what the 'last stream' in the following 2 cases:

Sure, but it depends on the population of the tables and the
available indexes and their selectivity. The way to find out
what the optimizer thinks is the best ordering is to get the
plan for the query on the data it will run against.

Your second query includes an EXISTS with a subquery. The
subquery will be executed ones for each iteration of the outer
loop.


Ann