A Hash Table Scan operator lets SQL Anywhere scan the build side of a hash join as if it were an in-memory table. This lets you convert a plan with this structure:
table1<seq>*JH ( <operator>... ( table2<seq> ) )
to the following structure, where
idx is an index that you can use to probe the join key values stored in the hash table:
table1<seq>*JF ( <operator>... ( HTS JNB table2<idx> ) )
When there are intervening operators between the hash join and the scan, it reduces the number of rows needed that must be processed by other operators.
This strategy is most useful when the index probes are highly selective, for example, when the number of rows in the build side is small compared to the cardinality of the index.
If the build side of the hash join is large, it is more effective to do a regular sequential scan.
The optimizer computes a threshold build size, similar to how it computes the threshold for the hash join alternate execution, beyond which the
(HTS JNB table<idx>) is treated as a sequential scan
(table<seq>) during execution.
The sequential strategy is used if the build side of the hash table has to spill to disk.