SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Hash Match


Hash Match

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: Administrators
Points: 63488 Visits: 19115
Comments posted to this topic are about the item Hash Match

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Raghavendra Mudugal
Raghavendra Mudugal
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2110 Visits: 2958
Interesting one, thank you for the post, Steve.

(Glad to be a part of the Steve's 12, who got it right. Now at least I take a hint that I am not smart and I am not dumb either... well lets go with the latter :-D)

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
gbritton1
gbritton1
Say Hey Kid
Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)

Group: General Forum Members
Points: 661 Visits: 864
Before I answered this question, I read

[url=http://technet.microsoft.com/en-us/library/ms189313(v=sql.105).aspx][/url]

It reads, in part:

The hash join has two inputs: the build input and probe input. The query optimizer assigns these roles so that the smaller of the two inputs is the build input.


then later:

The hash join first scans or computes the entire build input and then builds a hash table in memory.


I'm having trouble reconciling the official description with the "correct" answer.
bkmsmith
bkmsmith
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1022 Visits: 854
gbritton1 (5/1/2014)
Before I answered this question, I read

[url=http://technet.microsoft.com/en-us/library/ms189313(v=sql.105).aspx][/url]

It reads, in part:

The hash join has two inputs: the build input and probe input. The query optimizer assigns these roles so that the smaller of the two inputs is the build input.


then later:

The hash join first scans or computes the entire build input and then builds a hash table in memory.


I'm having trouble reconciling the official description with the "correct" answer.


+1

I followed the same reference in my investigation on this QOTD. Any insight on this seemingly discrepant definition would be appreciated.

Brian
sestell1
sestell1
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2555 Visits: 3463
bkmsmith (5/1/2014)
gbritton1 (5/1/2014)
Before I answered this question, I read

[url=http://technet.microsoft.com/en-us/library/ms189313(v=sql.105).aspx][/url]

It reads, in part:

The hash join has two inputs: the build input and probe input. The query optimizer assigns these roles so that the smaller of the two inputs is the build input.


then later:

The hash join first scans or computes the entire build input and then builds a hash table in memory.


I'm having trouble reconciling the official description with the "correct" answer.


+1

I followed the same reference in my investigation on this QOTD. Any insight on this seemingly discrepant definition would be appreciated.

Brian


As did I, after much digging to see what SQL Server defined as the 'build' input.
PHYData DBA
PHYData DBA
SSC Eights!
SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)

Group: General Forum Members
Points: 929 Visits: 537
See lots of discussion about why the right answer is...

Here is a reference to the right answer from TechNet BOL.
Looks like this answer to this question has not changed for at least 15 years...

http://technet.microsoft.com/en-us/library/aa237090(v=SQL.80).aspx

For any joins, use the first (top) input to build the hash table and the second (bottom) input to probe the hash table. Output matches (or nonmatches) as dictated by the join type. If multiple joins use the same join column, these operations are grouped into a hash team.
Cool
gbritton1
gbritton1
Say Hey Kid
Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)

Group: General Forum Members
Points: 661 Visits: 864
So, we have conflicting opinions! I suspect that the old Technet article is incorrect, or at least no longer correct. It only makes sense that SQL would build the hash table from the smaller input to minimize work.
jackson.fabros
jackson.fabros
SSC-Enthusiastic
SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)

Group: General Forum Members
Points: 191 Visits: 131
still unclear what the correct answer is. my textbook also mentions that "the hash table is built from the smaller input"
PHYData DBA
PHYData DBA
SSC Eights!
SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)

Group: General Forum Members
Points: 929 Visits: 537
gbritton1 (5/1/2014)
So, we have conflicting opinions! I suspect that the old Technet article is incorrect, or at least no longer correct. It only makes sense that SQL would build the hash table from the smaller input to minimize work.


Most likely this is convergence not conflict.

Here is a 2008 R2 article that says the same thing as the old one.
This and the last link I posted are actually listed as part of the SQL 2014 BOL but where not changed.
http://technet.microsoft.com/en-US/library/ms189582(v=SQL.105).aspx
For any joins, use the first (top) input to build the hash table and the second (bottom) input to probe the hash table. Output matches (or nonmatches) as dictated by the join type. If multiple joins use the same join column, these operations are grouped into a hash team.


There are some sources that say the QA will try to choose the smaller set as the build input, but here is some truth from SQL 2012 BOL.

http://technet.microsoft.com/en-us/library/ms173815.aspx
When using the HASH Join hint:
If a join hint is specified for any two tables, the query optimizer automatically enforces the join order for all joined tables in the query, based on the position of the ON keywords

So the first table in the ON statement is used unless REMOTE table exists or is specified.

IMHO: Considering the Join Hint documentation for HASH, the Query Optimizer would have to change the join order so that the smaller set is first (like any good Query writing utility would do) to do what the other article says it is doing.

Note: I could not find anything in the BOL articles unique to SQL 2014 so we are left to guess that SQL 2014 BOL just has pointers to the old articles because the information has not changed.
PHYData DBA
PHYData DBA
SSC Eights!
SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)

Group: General Forum Members
Points: 929 Visits: 537
jackson.fabros (5/1/2014)
still unclear what the correct answer is. my textbook also mentions that "the hash table is built from the smaller input"

Is this exactly what your textbook says?
Let's hope not since the build input should be made from the smaller set and the probe input from the remainder in a HASH JOIN.
A "hash table" would be something else but not sure how it would have different inputs. Hehe

There are even HASH JOIN where the build and probe inputs are the same thing...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search