Left joins usually give worse performance results than right joins.
Anyway, in specific situations (like yours) it may be more effective
because it forced the optimizer to generate another PLAN and this new
PLAN was more effective in your case. As all we know, the optimizer is
not perfect.

[]s

Carlos
WarmBoot Informatica - http://www.warmboot.com.br
Interbase-BR - http://www.interbase-br.com

IFL> Hi people,

IFL> Thanks for the answers, here are the results I got running your suggestions:

IFL> My statment:
IFL> PLAN JOIN (MO INDEX (RDB$FOREIGN209),T INDEX (RDB$PRIMARY206,SK_TURNO_DATA))
IFL> Reads 254
IFL> Writes 21
IFL> Execution Time 9.09 sec

IFL> Alexander Nevsky:

IFL> select
IFL> min(T.Data)
IFL> from
IFL> Turno T join
IFL> MaquinaOcupacao MO on MO.TurnoID = T.TurnoID
IFL> and T.Data > '2002-12-01' and
IFL> MO.MaquinaID = 17

IFL> you suggestion gives me the same plan and execution time

IFL> PLAN JOIN (MO INDEX (RDB$FOREIGN209),T INDEX (RDB$PRIMARY206,SK_TURNO_DATA))
IFL> Reads 254
IFL> Writes 17
IFL> Execution time varing from 8.02 to 10.3 sec



IFL> Tomislav Avramovic:

IFL> select
IFL> min(T.Data)
IFL> from
IFL> Turno T LEFT join
IFL> MaquinaOcupacao MO on (MO.TurnoID = T.TurnoID)
IFL> where
IFL> T.Data > '2002-12-01' and
IFL> MO.MaquinaID = 17

IFL> PLAN JOIN (T INDEX (SK_TURNO_DATA),MO INDEX (RDB$FOREIGN210))
IFL> Reads 422
IFL> writes 31
IFL> Execution Time 1.0035 sec
IFL> GOOD RESULTS !!!

IFL> Daniel Rail:

IFL> select
IFL> min(Turno.Data)
IFL> from
IFL> Turno
IFL> where
IFL> (Turno.Data > '2002-12-01') and
IFL> exists (select
IFL> *
IFL> from
IFL> MaquinaOcupacao
IFL> where
IFL> (MaquinaOcupacao.MaquinaID=17) and
IFL> (MaquinaOcupacao.TurnoID=Turno.TurnoID)
IFL> )

IFL> PLAN (MAQUINAOCUPACAO INDEX (RDB$FOREIGN209,RDB$FOREIGN210))
IFL> PLAN (TURNO ORDER SK_TURNO_DATA)
IFL> Reads 259
IFL> writes 23
IFL> Execution Time 0.0010 sec
IFL> INSTANT REPONSE YEAH!!!!!

IFL> Thanks for everybody for the help !

IFL> Just one thing, before I run the statment from Tomislav I thought that the
IFL> speed will not be so good because of the left join, I am a little impressed
IFL> that a left join produces better results than a natural join... Any comments ?

IFL> thanks again !

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

IFL> At 19:34 26/11/2002 +0000, you wrote:

>>--- In ib-support@y..., IB/FB List <iblist@t...> wrote:
>>
>> Alexandre, will
>>
>>select
>> min(T.Data)
>> from
>> Turno T join
>> MaquinaOcupacao MO on MO.TurnoID = T.TurnoID
>> and T.Data > '2002-12-01' and
>> MO.MaquinaID = 17
>>
>>be better?
>>
>>Best regards, Alexander V.Nevsky.
>>
>>
>>
>>To unsubscribe from this group, send an email to:
>>ib-support-unsubscribe@egroups.com
>>
>>
>>
>>Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/

IFL> To unsubscribe from this group, send an email to:
IFL> ib-support-unsubscribe@egroups.com



IFL> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/