At 11:44 30.04.2003 +0200, you wrote:

>Hi,
>
>On 29 Apr 2003 at 22:29, PODESTA Mariano APRE wrote:
>
> > i know i did not put any where clause, but the problem starts with:
> >
> > 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))
> >
> > again, natural is there. why?
>
>NATURAL scan is there because you requested the LEFT (OUTER) JOIN. Outer
>joins without WHERE filter condition are always evaluated by natural scan
>from obvious reasons.

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.

So Mariano, please tell us the full plan including the CU and NT tables. It
would also be good to know some statistics about these two tables as well
as the NS table.

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

Sure, I'll be there Pavel!

HTH,
Set