Specifying the join type

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:

    (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:

    (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:

    (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.