Immediate join spilling scheme for a database

A computer program product comprising:

• a computer readable storage medium having program code embodied therewith, the program code executable by a processor to:
• receiving a command to perform a join of an inner table and an outer table in a database;
• and
• performing the join using an immediate join spilling scheme, the immediate join spilling scheme comprising:
• spilling a plurality of hash tables to a disk memory, each hash table corresponding to a respective inner table partition;
• registering, by a first agent of a plurality of agents that are associated with the join, a set of inner table partitions that require processing by the first agent to complete the join for a first stride of rows of the outer table;
• determining whether a hash table corresponding to a registered inner table partition of the first agent has been loaded into a local memory by a second agent of the plurality of agents, wherein the local memory is smaller and faster than the disk memory;
• based on determining that the hash table corresponding to the registered inner table partition of the first agent has been loaded into the local memory by the second agent of the plurality of agents, performing the join of the loaded hash table and the first stride of rows of the outer table by the first agent, wherein the loaded hash table that is loaded in the local memory is not unloaded from the local memory until after the loaded hash table has been processed by each of the plurality of agents that have registered an inner table partition corresponding to the loaded hash table in a to-be-processed table;
• and
• unloading the loaded hash table from the local memory subsequent to the loaded hash table being processed by each of the plurality of agents that have registered the inner table partition corresponding to the loaded hash table in the to-be-processed table, wherein the loaded hash table is unloaded from the local memory by one of the plurality of agents other than the first agent.

A system comprising:

• a processor;
• a database;
• a disk memory;
• and
• a local memory, the local memory being closer to the processor than the disk memory;
• the processor configured to:
• receive a command to perform a join of an inner table and an outer table in the database;
• and
• perform the join using an immediate join spilling scheme, the immediate join spilling scheme comprising:
• spill a plurality of hash tables to the disk memory, each hash table corresponding to a respective inner table partition;
• register, by a first agent of a plurality of agents that are associated with the join, a set of inner table partitions that require processing by the first agent to complete the join for a first stride of rows of the outer table;
• determine whether a hash table corresponding to a registered inner table partition of the first agent has been loaded into the local memory by a second agent of the plurality of agents, wherein the local memory is smaller and faster than the disk memory;
• based on determining that the hash table corresponding to the registered inner table partition of the first agent has been loaded into the local memory by the second agent of the plurality of agents, perform the join of the loaded hash table and the first stride of rows of the outer table by the first agent, wherein the loaded hash table that is loaded in the local memory is not unloaded from the local memory until after the loaded hash table has been processed by each of the plurality of agents that have registered an inner table partition corresponding to the loaded hash table in a to-be-processed table;
• and
• unload the loaded hash table from the local memory subsequent to the loaded hash table being processed by each of the plurality of agents that have registered the inner table partition corresponding to the loaded hash table in the to-be-processed table, wherein the loaded hash table is unloaded from the local memory by one of the plurality of agents other than the first agent.