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 Donald
Accelerant, Inc.