>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 ?

Malcolm,

You can only get optimization on the WHERE condition. You did get optimization on JOINS, so everything seems to be correct.