JTYPE

Specifies the join algorithm as hash or merge.

Use the JTYPE hint to specify the algorithm the optimizer uses to join table data. If specified algorithm is not feasible, the optimizer ignores the hint and throws a warning.

Syntax

JOIN /*+JTYPE(join-type)*/

Arguments

join-type

One of the following arguments:

• H: Hash join

• M: Merge join, valid only if both join inputs are already sorted on the join columns, otherwise Vertica ignores it and throws a warning.

  Note

  The optimizer relies upon the query or DDL to verify whether input data is sorted, rather than the actual runtime order of the data.

• FM: Forced merge join. Before performing the merge, the optimizer re-sorts the join inputs. Two restrictions apply:

  • This option is valid only for simple join conditions. For example:
    

    SELECT /*+SYNTACTIC_JOIN*/ * FROM x JOIN /*+JTYPE(FM)*/ y ON x.c1 = y.c1;
    

  • Join columns must be of the same type and precision or scale. One exception applies: string columns can have different lengths

Requirements

• Queries that include the JTYPE hint must also include the SYNTACTIC_JOIN hint. Otherwise, the optimizer ignores the JTYPE hint and throws a warning.

• Join syntax must conform with ANSI SQL-92 join conventions.