Optimizer Overrides
Page 6 of 6

joinStrategy

Function

The joinStrategy property allows you to override the optimizer's choice of join strategy. The two types of join strategy are called nested loop and hash. In a nested loop join strategy, for each qualifying row in the outer table, Cloudscape uses the appropriate access path (index or table scan) to find the matching rows in the inner table. In a hash join strategy, Cloudscape constructs a hash table representing the inner table. For each qualifying row in the outer table, Cloudscape does a quick lookup on the hash table to find the matching rows in the inner table. Cloudscape has to scan the inner table or index only once to create the hash table.

Nested loop joins are useful in most situations.

Hash joins are useful in situations in which the inner table values are unique and there are many qualifying rows from the outer table.

The PROPERTIES clause must appear directly after the inner table.

NOTE: Use this optimizer override with the joinOrder property only. Do not let the optimizer choose the join order.

NOTE: When the optimizer automatically considers a hash join, it knows not to choose hash join if it estimates that the amount of memory required to build the hash table would exceed 1 MB. Forcing a hash join with this property does not take such memory use into consideration, and you can run out of memory.

Syntax

joinStrategy={NESTEDLOOP | HASH}

The words NESTEDLOOP and HASH are case-insensitive.

Default

Chosen by the optimizer.

Example

-- FlightAvailability is the inner table, so we specify
-- the joinStrategy after it
SELECT *
FROM PROPERTIES joinOrder = FIXED
    Flights AS fts, FlightAvailability AS fa
PROPERTIES joinStrategy=HASH
WHERE fts.flight_id = fa.flight_id
AND fts.segment_number = fa.segment_number

--segments_seatbookings is a view
SELECT * FROM PROPERTIES joinOrder=FIXED customizedtours, segments_seatbookings
PROPERTIES joinStrategy=NESTEDLOOP WHERE
customized_tour->begin = travel_date

Scope

Table optimizer-override property.