The first part refers to the WHERE clause optimization. There're no WHERE conditions specified for ManifestGrp and ManifestDtl and as result there's nothing to optimize for them. The indexes are used for JOINs optimization.

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