It is using those indexes for the join. Only filters (in the where clause) are mentioned in the rushmore optimization level.

It seems optimization is fine for those queries.

BTW, get rid off the deleted tag. It does not speed up your query (except in very specific circumstances). Full optimization does not mean that it is most optimal.

Walter,


>I am using SQL ShowPlan to investigate the performance of the following SQL SELECT:
>
>SELECT ManifestHdr.dManifest, ManifestHdr.lOpen, ManifestHdr.iQty,;
> COUNT(ManifestDtl.iPk) AS nScanned, ManifestHdr.iPk,
> Manifesthdr.iFkSystems;
> FROM SN!ManifestHdr;
> LEFT OUTER JOIN SN!ManifestGrp;
> ON ManifestGrp.iFkManHdr = ManifestHdr.iPk;
> LEFT OUTER JOIN SN!ManifestDtl;
> ON ManifestDtl.iFkManGrp = ManifestGrp.iPk;
> WHERE ManifestHdr.iFkSystems = ?vp_ManifestHdr_iFkSystems;
> GROUP BY ManifestHdr.iPk;
> ORDER BY ManifestHdr.dManifest
>
>I get the following output from SYS(3054,11)
>
>Using index tag Ifksystems to rushmore optimize table manifesthdr
>Using index tag Deleted to rushmore optimize table manifesthdr
>Rushmore optimization level for table manifesthdr: full
>Rushmore optimization level for table manifestgrp: none
>Rushmore optimization level for table manifestdtl: none
>Joining table manifesthdr and table manifestgrp using index tag Ifkmanhdr
>Joining intermediate result and table manifestdtl using index tag Ifkmangrp
>
>Why are manifestgrp and manifestdtl tables not optimized if I have indexes on:
>ManifestGrp.iFkManHdr and
>ManifestDtl.iFkManGrp ?