Hash table probe

 

A hash table probe operation is used to retrieve rows from a temporary hash table based upon a probe lookup operation.

The optimizer initially identifies the keys of the temporary hash table from the join criteria specified in the query. This is done so that when the hash table probe is performed, the values used to probe into the temporary hash table will be extracted from the join-from criteria specified in the selection. Those values will be sent through the same hashing algorithm used to populate the temporary hash table in order to determine if any rows have a matching (equal) value. All of the matching join rows are then returned to be further processed by the query.

Table 1. Hash table probe attributes
Data access method Hash table probe
Description The temporary hash table is quickly probed based upon the join criteria.
Advantages

  • Provides very quick access to the selected rows that match probe criteria

  • Reduces the random I/O to the table generally associated with longer running queries that use an index to collate the data

  • Selection can be performed before generating the hash table to subset the number of rows in the temporary object
Considerations Generally used to process equal join criteria. Can perform poorly when the entire hash table does not stay resident in memory as it is being processed.
Likely to be used

  • When the use of temporary results is allowed by the query environmental parameter (ALWCPYDTA)

  • When the data is required to be collated based upon a column or columns for join processing

  • The join criteria was specified using an equals (=) operator
Example SQL statement 
SELET * FROM Employee XXX, Department YYY WHERE XXX.WorkDept = YYY.DeptNbr OPTIMIZE FOR ALL ROWS
Messages indicating use There are multiple ways in which a hash probe can be indicated through the messages. The messages in this example illustrate how the SQL Query Engine will indicate a hash probe was used.

  • Optimizer Debug: 
     CPI4327 -- File EMPLOYEE processed in join             position 1. 
 CPI4327 -- File DEPARTMENT processed in join             position 2.

  • PRTSQLINF: 
     SQL4007 -- Query implementation for join             position 1 table 1. 
 SQL4010 -- Table scan access for table 1. 
 SQL4007 -- Query implementation for join             position 2 table 2. 
 SQL4010 -- Table scan access for table 2.
SMP parallel enabled Yes
Also referred to as Hash Table Probe, Preload

Hash Table Probe Distinct

Hash Table Probe Distinct, Preload

Visual Explain icon

Hash table probe icon

The hash table probe access method is generally considered when determining the implementation for a secondary table of a join. The hash table is created with the key columns that match the equal selection or join criteria for the underlying table. The hash table probe allows the optimizer to choose the most efficient implementation to select the rows from the underlying table without regard for any join criteria. This single pass through the underlying table can now choose to perform a Table Scan or use an existing index to select the rows needed for the hash table population.

Since hash tables are constructed so that the majority of the hash table will remain resident within main memory, the I/O associated with a hash probe is minimal. Additionally, if the hash table was populated with all necessary columns from the underlying table, no additional Table Probe will be required to finish processing this table, once again causing further I/O savings.

 

Parent topic:

Temporary hash table

 

Related concepts


Nested loop join implementation