On 30 Apr 2003 at 12:23, Svein Erling Tysvaer wrote:

> > > select
> > > cu.id_cuenta
> > > from cuenta cu
> > > join notificacion nt on nt.id_cuenta = cu.id_cuenta
> > > join noti_recipiente nr on nr.id_notificacion = nt.id_notificacion
> > > join recipiente re on re.id_recipiente = nr.id_recipiente
> > > join noti_servicio ns on ns.id_noti_servicio = re.id_noti_servicio
> > > left join noti_reglas ng on ng.id_noti_reglas = nt.id_noti_reglas and
> > > ng.activo = 1
> > > where
> > > cu.id_cuenta = 696 and
> > > nt.id_evento = 123
> > >
> > > PLAN JOIN (JOIN (NS NATURAL,RE INDEX (FK_RECIPIENTE_NS),NR
> > >
> > INDEFK_NOTI_RECIPIENTE_REN57)PK_NOTIFICACIONB$PRIMARY48,RDB$FOREIGN5PK_CUENT
> > > A(RDB$PRIMARY1)),NG INDEX (PK_NOTI_REGLAS))
> > >
>
> Pavel, I think you're wrong this time. The left outer join relates to
> noti_reglas (ng), which correctly uses an index. The where-clause, however,
> limits the left side of the left join (with all the other tables) and for
> some reason Firebird thinks its best bet is to use natural on
> the noti_servicio (ns) table. Though the plan doesn't quite make sense.
> It doesn't mention the cuenta (cu) or notificacion (nt) tables, which
> should contain vital information as to why Firebird chose the plan it did.

Sorry, but I'm not wrong here :-). There is always more than one way how
to join tables, and optimizer looks at (almost) all permutations. Note
that LEFT points to NS, not NG which is RIGHT side of join. Becuse OUTER
join without where condition usually make a big intermediate product, is
pushed up to the root of the join chain and to the left (join is usually
a loop over left side with lookup for relevant row from right side, where
bigger set is always on left).

Best regards
Pavel Cisar

See you at the First European Firebird Conference in May in Fulda,
Germany
http://www.firebird-conference.com

http://www.ibphoenix.com
For all your upto date Firebird and
InterBase information