Huh?

I know you're one of the big gurus on Fb and all you write are pretty
accurate (unlike me, who may give wrong advice). Still, I don't understand
what I think I read from what you're writing.

Why must this query use natural for table NS? Table NS belongs to the bunch
of tables at the left side of the join and so does the where clause. I
would then think that the leftmost table of the plan could be one of the
tables referenced in the where clause. I agree there isn't anything to do
with the table to the right of the left join, but that table does not
appear to be a problem. Are you saying that the plan would change more than
just eliminating the table to the right of the left join if this table was
removed from the query?

Set

At 15:27 30.04.2003 +0200, Pavel wrote:

>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).

>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 (RDB$FOREIGN56),NR INDEX
>(RDB$FOREIGN57),NT INDEX (RDB$PRIMARY48,RDB$FOREIGN50),CU INDEX
>(RDB$PRIMARY1)),NG INDEX (PK_NOTI_REGLAS))