HSJOIN (Hash join) node

This node represents hash joins for which the qualified rows from tables are hashed.

Node name: HSJOIN

Represents: A hash join for which the qualified rows from tables are hashed to allow direct joining, without pre-ordering the content of the tables.

A join is necessary whenever more than one table is referenced in a FROM clause. A hash join is possible whenever a join predicate equates columns from two different tables. The join predicates need to be exactly the same data type. Hash joins can also arise from a rewritten subquery, as is the case with nested loop joins.

A hash join does not require that the input tables are ordered. The join is performed by scanning the inner table of the hash join and generating a lookup table by hashing the join column values. It then reads the outer table, hashing the join column values, and checking in the lookup table generated for the inner table.

Performance suggestions:
  • Use local predicates (that is, predicates that reference one table) to reduce the number of rows to be joined.
  • Increase the size of the sort heap to make it large enough to hold the hash lookup table in memory.
  • If statistics are not current, update them by using the RUNSTATS command.

Feedback