The MERGE JOIN hint directs the optimizer to choose the merge join access plan in a SELECT query for all tables listed in the FROM clause. The MERGE JOIN hint is used when two tables are approximately equal in size and the data is distributed equally. It is faster than a LOOP JOIN when an equal amount of rows are joined. For joining data, MERGE JOIN supports a maximum of three tables. The joining table is ordered by joining columns and combining the results of the columns.
You can use this hint when the data is sorted by a join key and the nested loop join performance is not adequate. The optimizer selects the merge join only where there is an equal predicate between tables (for example, "table1.col1 = table2.col1"). Otherwise, the optimizer selects LOOP JOIN even if the MERGE JOIN hint is specified.
When data is not sorted before performing the merge operation, the solidDB query executor sorts the data.
Keep in mind that the merge join with a sort is more resource intensive than the merge join without the sort.
For example:
SELECT -(* vendor(SOLID), product(Engine), option(hint) -- MERGE JOIN *)-- * FROM TAB1, TAB2 WHERE TAB1.I >= TAB2.I;