Adaptive Server can perform either nested-loop or merge joins. The g_join operator leaves the optimizer free to choose the best join algorithm, based on costing. To specify a nested-loop join, use the nl_g_join operator; for a sort-merge join, use the m_g_join operator. Abstract plans captured by Adaptive Server always include the operator that specifies the algorithm, and not the g_join operator.
Note that the “g” that appears in each operator means “generic,” meaning that they apply to inner joins and outer joins; g_join and nl_g_join can also apply to existence joins.
This query specifies a join between t1 and t2:
select * from t1, t2
where c12 = c21 and c11 = 0
This abstract plan specifies a nested-loop join:
(nl_g_join
(i_scan i_c11 t1)
(i_scan i_c21 t2)
)
The nested-loop plan uses the index i_c11 to limit the scan using the search clause, and then performs the join with t2, using the index on the join column.
This merge-join plan uses different indexes:
(m_g_join
(i_scan i_c12 t1)
(i_scan i_c21 t2)
)
The merge join uses the indexes on the join columns, i_c12 and i_c21, for the merge keys. This query performs a full-merge join and no sort is needed.
A merge join could also use the index on i_c11 to select the rows from t1 into a worktable; the merge uses the index on i_c21:
(m_g_join
(i_scan i11 t1)
(i_scan i21 t2)
)
The step that creates the worktable is not specified in the plan; the optimizer detects when a worktable and sort are needed for join-key ordering.
