Join Hints

  • Article

Summary

In addition to the JOIN operators, U-SQL optionally allows the query author to provide hints on the type of join to choose and what parallel join execution to use.

Syntax

Join_Hints :=                                                                                            
     [Parallel_Join_Hint] [Join_Algorithm_Hint].

Parallel_Join_Hint :=  
     'SERIAL' | 'PAIR' | 'BROADCASTLEFT' | 'BROADCASTRIGHT' | 'FULLCROSS'.

Join_Algorithm_Hint :=  
     'MERGE' | 'HASH' | 'LOOP' | 'INDEXLOOKUP'.

These hints impact the plan generation of the query optimizer. If the optimizer cannot find a succession of optimizer rules to satisfy the requested hints, for example because the hints are not applicable to a specific JOIN, a NO PLAN error will be raised.

These hints are best used with care, since specifying a certain hint will make it impossible for the optimizer to find a better plan. They should only be used if the default plan is not optimal.

See Also