Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 11.0.1 » SQL Anywhere Server - SQL Usage » Query Processing » Query optimization and execution » Query execution algorithms » Table access methods

 

HashTableScan method (HTS)

HashTableScan scans the build side of a hash join as if it were an in-memory table, thereby converting a plan with first structure below, to that of the second structure below, where idx is an index that can be used to probe the join key values stored in the hash table:

table1<seq>*JH ( <operator>... ( table2<seq> ) )

table1<seq>*JF ( <operator>... ( HTS JNB table2<idx> ) )

When there are intervening operators between the hash join and the scan, a hash table scan 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.

Note

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. If the number of rows in the build side exceeds this threshold, HashTableScan is abandoned and the (HTS JNB table<idx>) is treated as a sequential scan (table<seq>) during execution.

Note

The sequential strategy is used if the build side of the hash table has to spill to disk.