Franz J Fortuny wrote:

> --- In firebird-support@yahoogroups.com, Alexandre Benson Smith
> <iblist@...> wrote:
>
>> Well, maybe I am wrong, but from what I read (or better, from what I
>> understood) he always is comparing the "IN <constant>" vs "IN
>> <subquery>" as he tells about plans and first/second query.
>>
>>
>
> Alexandre:
>
> This comparison is only the last part of the whole problem.
>
> In previous messages we have concluded that:
>
> 1) JOINS must be placed before LEFT OUTER JOINS
>

Yes, but *any* kind of outer join (left, right or full)

> 2) If a VIEW contains LEFT OUTER JOINS (my case), then anything YOU
> JOIN to such view will NOT USE the correct plan.
>

If your filter criteria on the joined tables after the view yes, if your
filter criteria is on the tables before the outer index on the view FB
will use the "correct" plan.

> The discussions about IN (list) and IN (query) was the consequence of
> trying to find other ways for the proper indexes to be used. As a
> matter of fact, if the query does NOT contain LEFT OUTER JOINS, the IN
> (query) DOES USE the correct indexes!
>

I was talking about the first query you showed up, there is no views
involved. With your last message, you showed a query joining to a view
with left joins, so the Set's suggestion would not work as you found out.

> A very good lesson today. Thanks to all who participated.
>
> Franz J Fortuny

As a comment:
I second Set's comments about the order of join's that should be not
relevant. I learn the hard way that this works this way :-(

Another optimizer improvement that I want to see regarding outer joins
is the following:
Suppose I have a view like this:

Create view V (F1, F2, F3) as
select
TableA.Field1, TableA.Field2, TableB.Field3
from
TableA left join
TableB on (TableA.SomeField = TableB.SomeField)

Let's suppose that all fields are indexed with good selectivity.

if I do:
Select * from V where F1 = X

I would get an indexed search on TableA and then an indexed Search on
TableB what gives me optimal performance.

But if I do
Select * from V where F3 = Y

I would get a natural scan on TableA and then an indexed Search on
TableB what gives me sub-optimal performance. The optimal plan would be
Indexed Search on TableB than another Indexed search on TableA and the
outer join be treated as an inner join

In this case the outer join could be promoted to an inner join if the
filter criteria is applied to the outer table and the value is not null.
I think it was discussed sometime ago and that Arno's agreed that it
would be a good improvement, but I think it was left for a better time.


see you !

--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br