question

Dakro avatar image
Dakro 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
joinsexecution-planhash
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

CirqueDeSQLeil avatar image
CirqueDeSQLeil answered
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).
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Dakro avatar image
Dakro answered
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
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
2 Likes 2 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.