Hi,

> I've the following query witch is running correctly:
>

SELECT
*
FROM
LIN_PROG_EMB_DESEMB L
JOIN CAB_PROG_EMB_DESEMB C ON (L.COD_CAB = C.COD_CAB)
JOIN ART ART ON (L.COD_ART = ART.COD_ART)
JOIN ENTID E ON (L.COD_ENTID = E.COD_ENTID)
JOIN PROC P ON (C.COD_PROC = P.COD_PROC)
JOIN ARMAZ ARM ON (L.COD_ARMAZ = ARM.COD_ARMAZ)
LEFT JOIN SUB_ART SA ON (SA.COD_SUB_ART = L.COD_SUB_ART)
LEFT JOIN DIMENS_SUB_ART DSA1 ON (SA.COD_DIMENS_SUB_ART1 =
DSA1.COD_DIMENS_SUB_ART)
LEFT JOIN DIMENS_SUB_ART DSA2 ON (SA.COD_DIMENS_SUB_ART2 =
DSA2.COD_DIMENS_SUB_ART)
LEFT JOIN DIMENS_SUB_ART DSA3 ON (SA.COD_DIMENS_SUB_ART3 =
DSA3.COD_DIMENS_SUB_ART)
WHERE
L.COD_ART ='1'

> The selected PLAN is:
>
> PLAN JOIN (JOIN (JOIN (JOIN (JOIN (ARM NATURAL,L INDEX
> (RDB$FOREIGN185,RDB$FOREIGN186),E INDEX (RDB$PRIMARY53),C INDEX
> (RDB$PRIMARY180),P INDEX (RDB$PRIMARY101),ART INDEX
> (RDB$PRIMARY91)),SA INDEX (RDB$PRIMARY96)),DSA1 INDEX
> (RDB$PRIMARY94)),DSA2 INDEX (RDB$PRIMARY94)),DSA3 INDEX
> (RDB$PRIMARY94))
>
> The table ARMAZ is indexed on the field COD_ARMAZ (Primary Key), but
> this index isn't used.

That could be correct, because the optimizer chooses the table with most
expensive reads (cost) to start with from all inner joined tables
(LIN_PROG_EMB_DESEMB, CAB_PROG_EMB_DESEMB, ART, ENTID, PROC, ARMAZ) and it
seems ARMAZ is the most expensive one.

> If I change the INNER JOIN to LEFT JOIN for the table ARMAZ, then the
> index is used.
>
> At the moment, the table ARMAZ has only 5 records and, in the future,
> it should have about 10/15.

The optimizer recalculates plan on every statement thus if content has
changed then the PLAN can change too. Note! That selectivity what optimizer
uses to calculate cost is not updated after every insert/delete. That means
it could be that selectivity value is not accurate. Although the change
between 5 and 15 records is not big ;-). You can force recalculate of index
with SET STATISTICS statement if needed.

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird links :
http://www.firebirdsql.com
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/


Nederlandse firebird nieuwsgroep :
news://80.126.130.81