Systems and methods for performing a nested join operation

A non-transitory computer readable medium to store executable instructions for performing a nested join operation, the executable instructions comprising:

• a query optimizer (56) comprising:
• a query evaluation component (62) to evaluate an SQL query to determine if it would be effective to compute partitioning key values for a join key of an outer data source using a partitioning function used to compute partitioning key values for the join column in an inner table;
• and
• a repartitioning component (64) to repartition the outer table using the partitioning function if it is determined to be effective to compute the partitioning key values and to establish a join process for each of a plurality of partitions of the inner table, a given partition of the inner table representing a plurality of partitioning key values;
• and
• a query execution engine (58) to route each row from the outer data source to a join process according to its associated partitioning key value, such that each row is routed to a join process associated with the partition representing the partitioning key value of the row, probe the inner table to return a row from the inner table having the join key value associated with the row from the outer data source, and join the row from the outer data source and the row from the inner table to form a row in a combined table, the query execution engine further comprising:
• a skew handling component (66) to alter the routing of row from the outer data source if the partitioning key value associated with the row from the outer data source occurs in the outer data source with a frequency greater than a threshold value, and
• a cache maintenance component (68) to cache the row from the inner table in a probe cache at the join process.

A system comprising:

• memory (26) to store executable instructions for performing a nested join operation on an inner table and an outer data source on an associated join column, the executable instructions comprising:
• a query optimizer (56) to repartition the outer table, using a hash function associated with the inner table and establish a join process for each of a plurality of partitions of the inner table, a given partition of the inner table representing a plurality of hash values;
• and
• a query execution engine (58) to route each row from the outer data source to a join process according to an associated partitioning key value, such that each row is routed to a join process associated with the partition representing the partitioning key value of the row, probe the inner table to return a row from the inner table having the join key value associated with the row from the outer data source, and join the row from the outer data source and the row from the inner table to form a row in a combined table;
• and
• a processor (18) to execute the executable instructions in the memory.