Switching between a non-partitioned hash join and a partitioned hash join based on an amount of available memory

Patent metadata

Field	Value
Publication number	US-10157193-B2
Application number	US-201615060086-A
Country	US
Kind code	B2
Filing date	Mar 3, 2016
Priority date	Mar 3, 2016
Publication date	Dec 18, 2018
Grant date	Dec 18, 2018

Abstract

A method implemented by at least one processing device, a processing device, and a computer program product are provided for adjusting hash partitions for a hash join operation. An amount of rows within each of an initial number of hash partitions is determined during assignment of respective rows to respective hash partitions. The initial number of hash partitions is adjusted to a final number of hash partitions based on the amount of rows within each of the initial number of hash partitions and an amount of available memory. The adjusting further includes determining the final number of hash partitions based on the amount of rows within each of the initial number of hash partitions and the amount of available memory, and assigning the rows to the final number of hash partitions. The hash join operation is then performed.

First claim

We claim as our invention:

1. A processing device comprising:
at least one processor;
a memory; and
a communication bus connecting the at least one processor with the memory, wherein the memory has stored therein instructions, which when executed by the at least one processor cause the processing device to perform a method comprising:
creating simulated hash partitions, each hash partition of an initial number of hash partitions corresponding to a respective plurality of simulated hash partitions;
determining an amount of rows of each of the simulated hash partitions;
determining an amount of rows within each respective hash partition of the initial number of hash partitions, the determined amount of rows of the each respective hash partition being based on partitioning inner leg rows of a hash join operation into the initial number of hash partitions, the initial number of hash partitions being based on static information and a historical amount of available memory;
adjusting the initial number of hash partitions to a final number of hash partitions based on the amount of rows within each of the initial number of hash partitions and an amount of available memory at runtime, the adjusting further comprising:
determining the final number of hash partitions based on the amount of rows within each of the initial number of hash partitions and the amount of available memory at runtime, the determining the final number of hash partitions further comprising:
when each of the initial number of hash partitions fits into the amount of available memory at runtime, performing:
 combining ones of the initial number of hash partitions when the combined ones of the initial number of hash partitions are determined to fit into the amount of available memory at runtime, resulting in the final number of hash partitions being less than the initial number of hash partitions, thereby increasing processing performance during the hash join operation by decreasing a frequency at which respective hash partitions are loaded into the memory; and
when at least one of the hash partitions does not fit into the amount of available memory at runtime, performing:
 increasing an amount of hash partitions to the final number of hash partitions, the final number being larger than a current number of hash partitions so that each of the final number of hash partitions fits into the amount of available memory at runtime; and

performing the hash join operation with the final number of hash partitions.

2. The processing device of claim 1 , wherein
the determined amount of rows of the each respective hash partition is based on the determined amount of rows of the corresponding simulated hash partitions.

3. The processing device of claim 1 , wherein an amount of rows corresponding to each of the final number of hash partitions is based on the determined amount of rows of each of the corresponding simulated hash partitions.

4. The processing device of claim 2 , wherein the final number of hash partitions is one hash partition.

5. The processing device of claim 1 , wherein each of the final number of hash partitions includes a respective distinct hash table.

6. A computer program product comprising:
a computer readable storage medium having computer readable program code embodied therewith for execution on a processing system, the computer readable program code being configured to be executed by the processing system to:
create simulated hash partitions, each hash partition of an initial number of hash partitions corresponding to a respective plurality of simulated hash partitions;
determining an amount of rows of each of the simulated hash partitions;
determine an amount of rows within each respective hash partition of the initial number of hash partitions, the determined amount of rows of the each respective hash partition being based on partitioning inner leg rows of a hash join operation into the initial number of hash partitions, the initial number of hash partitions being based on static information and a historical amount of available memory;
adjust the initial number of hash partitions to a final number of hash partitions based on the amount of rows within each of the initial number of hash partitions and an amount of available memory at runtime, the adjusting further comprising:
determine the final number of hash partitions based on the amount of rows within each of the initial number of hash partitions and the amount of available memory at runtime, the determining the final number of hash partitions further comprising:
when each of the initial number of hash partitions fits into the amount of available memory at runtime:
 combine ones of the initial number of hash partitions when the combined ones of the initial number of hash partitions are determined to fit into the amount of available memory at runtime, resulting in the final number of hash partitions being less than the initial number of hash partitions, thereby increasing processing performance during the hash join operation by decreasing a frequency at which respective hash partitions are loaded into the memory; and
when at least one of the hash partitions does not fit into the amount of available memory at runtime:
 increase an amount of hash partitions to the final number of hash partitions, the final number being larger than a current number of hash partitions so that each of the final number of hash partitions fits into the amount of available memory at runtime; and

perform the hash join operation with the final number of hash partitions.

7. The computer program product of claim 6 , wherein
the determined amount of rows of the each respective hash partition is based on the determined amount of rows of the corresponding simulated hash partitions.

8. The computer program product of claim 6 , wherein an amount of rows corresponding to each of the final number of hash partitions is based on the determined amount of rows of each of the corresponding simulated hash partitions.

9. The computer program product of claim 6 , wherein each of the final number of hash partitions includes a respective distinct hash table.

Assignees

• IBM

Inventors

• Attaluri Gopi K
• Raman Vijayshankar
• Sharpe David C

Classifications

• G06F16/27

  Replication, distribution or synchronisation of data between databases or within a distributed database system; Distributed database system architectures therefor · CPC title

• G06F16/2456 Primary

  Join operations · CPC title

• G06F17/3033

  Physics · mapped topic

• G06F17/30283 Primary

  Physics · mapped topic

• G06F17/30486

  Physics · mapped topic