Adaptive Server can perform nested-loop, merge, or hash joins. The join operator leaves the optimizer free to choose the best join algorithm, based on costing. To specify a nested-loop join, use the nl_join operator; for a merge join, use the m_join operator, and for a hash join, use the h_join operator. Abstract plans captured by Adaptive Server always include the operator that specifies the algorithm, and not the join operator.
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_join
(i_scan i_c11 t1)
(i_scan i_c21 t2)
)
The nested-loop plan uses the index i_c11to 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_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 only the matching rows, but then a sort is needed to provide the needed ordering.
(m_join
(sort
(i_scan i_c11 t1)
)
(i_scan i_c21 t2)
)
Finally, this plan does a hash join and a full table scan on the inner side:
(h_join (i_scan i_c11 t1) (t_scan t2) )
