SPD Server Join Planner

The SPD Server Join Planner is a rules-based planner. The join planner searches for a pairwise equijoin match in a particular order. The first plan that the join planner finds that meets requirements is selected. If the join is an n-way join, each pairwise join of the n-way join is planned until all of the joins are exhausted.

Each pairwise join follows the same selection order to determine which join plan is selected. The order of the join planner for a pairwise equijoin is as follows:

SPD Server searches for an acceptable star schema optimization.
SPD Server searches for an index join.
SPD Server searches for a hash join.
SPD Server searches for a merge join, with preferences given to parallel merge joins.
SPD Server searches for a sequential loop join.

There are several SPD Server SQL reset switches that affect the join planner:

The SPD Server star schema optimization reset switch NOSTARJOIN disables star joins.
The index join reset switch INDEX_SELECTIVITY can change the relative usefulness of the index for the join type. High index selectivity settings can affect whether the join planner chooses the index join.
The hash join reset switch MAXHASHJOINS can increase or decrease the number of hash joins that can be planned for a single query. The hash join BUFFERSIZE reset switch can increase or decrease the amount of memory that is allocated for hash joins.
The merge join reset switch NOPLLJOIN disables parallel merge joins.

You can favor a join plan by using the JOINTECH_PREF reset switch. Favoring a join plan does not guarantee that the favored join plan will be used, however. For example, if you favor a hash join, SPD Server still requires sufficient BUFFERSIZE memory allocation to plan the hash join.

You can use the DETAILS= "what_join$why_join$" reset switch to print additional information in the SAS log to determine what join method the SPD Join planner selected, and why it was selected. The why_join information includes how the reset switches affected the join planner.