What cases benefit from the Reduce, Replicate, and Redistribute join hints?
In general terms we use a join hint is to correct a failure of the optimizer to identify a better query plan. This can happen for many reasons which broadly boil down to optimization being a &quot;good enough&quot; approach constrained by a time budget for finding a plan.
Reduce
&quot;also called a semi-join hint&quot;. With a semi-join we are only interested in whether or not any matching row exists in the other table. Indeed, it is often because the query has an EXISTS() predicate. If there is one match or one million the result will be the same - the single row from the left (outer) table will be returned.
As an example, we may want to query all Customers who have ever placed an Order. Let's say Customer is a small table but Order is huge and spread over many nodes. It would be wasteful to copy all that data to wherever Customer is held when all we need is to know if at least one row exists for each Customer. The REDUCE hint provides this. Conceptually it is select distinct CustomerId from Order run on each node holding Order, which is relatively fast.
Replicate
This is the converse of REDUCE. Here a table is copied en masse from its &quot;home&quot; node to all nodes holding data from the table on the right-hand side of the join.
For this example we want the total value of all orders, summed by Customer city. We only have a relatively few customers which comfortably fit on a single node. The Orders table, however, is huge and spans many nodes. To satisfy the query we have to get both Customer and Order data onto the same node. It will be more efficient to copy Customer, since it is smaller. The REPLICATE hint forces that behaviour, making a complete copy of the Customer table on each node holding Order rows.
Redistribute
This is another way of stating how rows should be moved from their &quot;home&quot; node to another node so join predicates can be evaluated. This time we are asking the server to copy the data and distribute that copy according to some new distribution function such that the copied rows will end up on the right node to complete the join.
Say now we have a retail e-commerce site with lots of customers and lots of orders. Customers are distributed by CustomerId and Orders distributed by OrderDate. If we now want to join on Customer.CustomerId = Order.CustomerId the most efficient plan may be to copy Order and re-distribute according to CustomerId, then pass each new partition to the corresponding node. This differs from REPLICATE as there the whole table is copied to every node whereas here a portion of each node's data is copied over to other nodes. The specific way the re-distribution is handled will depend in the current distribution and the cardinalities.