The 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 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:
-
The server searches for an acceptable
star schema optimization.
-
The server searches
for an index join.
-
The server searches
for a hash join.
-
The server searches
for a merge join. Preferences are given to parallel merge joins.
-
The server searches
for a sequential loop join.
There are several server
SQL reset switches that affect the join planner:
-
The 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, the 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 which 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.