HSJOIN

HSJOIN

Operator 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 there is more than one table referenced in a FROM clause. A hash join is possible whenever there is a join predicate that equates columns from two different tables. The join predicates need to be exactly the same data type. Hash joins may also arise from a rewritten subquery, as is the case with NLJOIN.

A hash join does not require the input tables be 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.

For more information, see the section on join concepts in the Administration Guide.

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 using the runstats command.