Conversation started May 21, 2013 at 21:46.
Paul White
Paul White
May 21, 2013 21:46
@swasheck IIRC prior to 2012 union hints didn't always work as expected with UNION ALL - both CONCAT and MERGE options are available for that. Otherwise, the hints work pretty much as you'd expect. Did you have a specific question?
@Marian Oh right ok. I just read the bit of the transcript where I was mentioned :)
swasheck
swasheck
@PaulWhite thanks. i dont have a specific question but was just wondering what these hints actually did in terms of unions. so a HASH union would create a hash of the sets and union approriately ... merge would sort and compare the sets. right? what is the behavior of a CONCAT UNION?
Paul White
Paul White
CONCAT can only do UNION ALL. If the query requires a duplicate-removing UNION, there will be extra operators in the plan to do that.
e.g. Sort Distinct or an Aggregate
swasheck
swasheck
@PaulWhite right ... i guess i'm just confused as to its purpose. isnt a UNION ALL functionally concatenating two sets? or am i talking out of the wrong end here
Paul White
Paul White
Concatenating two multi-sets, yes. Multi-sets may include duplicates.
swasheck
swasheck
ok. i'm dense. could you help me with a use case for when you'd (theoretically) want to hint a UNION ALL ... CONCAT UNION query?
by the way, you can tell me to buzz off if you'd rather. i can be an unintentional pest. or i can post a question for your efforts
Paul White
Paul White
May 21, 2013 22:00
For UNION ALL, the optimizer can choose between CONCAT and MERGE UNION. Most often it will choose CONCAT, unless the two inputs to the union are already guaranteed to be duplicate-free, sorted on the merge key, and/or preserving key order is useful, in which case it will choose a merge union. I have rarely had to hint UNION ALL to get a CONCAT over a MERGE UNION.
swasheck
swasheck
@PaulWhite duurrrrrrrr. thanks. it finally clicked.
Paul White
Paul White
Union and union all implemented by merge join looks confusing. There's no merge join going on, it just so happens that the merge logic can do union and union all as well, so the same iterator is reused, it just runs in a different mode internally.
Merge union naturally removes duplicates in both inputs, but it does not de-dupe each input individually. So, if the inputs might contain duplicates, you'll see an aggregate before the merge union to de-dup each input.
swasheck
swasheck
interesting.
Paul White
Paul White
Hash union is quite rare. It builds a hash table on one input and eliminates duplicates as it goes, just like hash agg does.
It then processes the second input, probing the hash table for matches as usual. If the probe row is not found, it is returned, otherwise discarded. Hash union does not eliminate dups from the probe input, so there will be an extra operator to do that, unless the optimizer knows that input is already dupe-free.
 
Conversation ended May 21, 2013 at 22:05.