asked
Execution Plan Hash Match Confusion
Hi, I dont understand the predicate on the highlighted index in the execution plan below. the index that is being scanned does not have "tranID", that variable is returned from table TranState above. how is the index for TranGTM is PROBED on TranID when it a none clustered index on "AssetID". ![alt text][1] [1]: /storage/temp/1679-execution+plan+bitmap.png
Not seeing your query, it appears that you are performing a join on tranID between EFT.TranGTM and your TranState object (table or view). The use of tranID in the predicate in the execution plan does not mean that it is the output of the query, but that the column is being used to "filter" the data in that table. That filtering is most commonly done via the conditions of your join or your query predicate (where clause).
apologies here is the query. i think i am not understanding execution plans very well. the predicate on TranGTM confused me. where it says PROBE[bitmap1017]... SELECT tranDateTime, postDate, TJ.glAccount + ' ' + TJ.debitCreditInd + 'R' header, SUM(ISNULL(ABS(TJ.journalAmount),0)) Amount FROM EFT.AssetGTM (NOLOCK) GTM JOIN EFT.TranGTM (NOLOCK) TRANGTM ON GTM.assetID = TRANGTM.assetID JOIN EFT.TranJournal (NOLOCK) TJ ON TRANGTM.tranID = TJ.tranID JOIN EFT.Transtate (NOLOCK) TRANSTATE ON TRANGTM.tranID = TRANSTATE.tranID WHERE GTM.tranDateTime BETWEEN '2011-01-01 00:00:00' AND GETDATE() AND ISNULL(TJ.Reclass, 'No') <> 'Yes' AND TRANSTATE.machineID IN (93,5) AND TRANSTATE.stateID IN (197,4) GROUP BY --GTM.tranDateTime,TJ.postDate,TJ.debitCreditInd,TJ.glAccount,PM.aliasID,P.[partner] LEFT(CONVERT(VARCHAR, GTM.tranDateTime, 120), 10),TJ.postDate,TJ.debitCreditInd,TJ.glAccount
Yeah that indicates the conditions of the probe. You have an outer table (TranState) that is providing tranID to the inner table of the "scan" / "probe". Take the tranID from the TranState table and pass it to the TranGTM table (caused by your join statement) and return everything that matches. Call that a 50k foot view, but it basically boils down to your query joining the two tables on tranID.
15 People are following this question.
