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.
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.
The sequential strategy is used if the build side of the hash table has to spill to disk.
Discuss this page in DocCommentXchange.
|Copyright © 2010, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.0|