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

SQL Anywhere 10.0.1 » SQL Anywhere Server - SQL Usage » Query Optimization and Execution » Query execution algorithms » Table access algorithms

Table access algorithms Next Page

Hash Table Scans


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.

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, beyond which 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.