Spilling a hash set structure to disk in conjunction with executing a set operation via a database system
A database system is operable to, in response to the spill to disk condition being met, spill a hash set structure to disk based on partitioning a set of hash values of the hash set structure into a plurality of hash buckets written to disk memory resources. Each of a remaining incoming subset of input rows of a plurality of input rows are processed while the spill to disk condition is met based on storing an un-hashed row value of the each of the remaining incoming subset of input rows into one of a plurality of row buckets written to the disk memory resources. In response completing processing of all of the plurality of input rows, output rows of an output row set are generated based on processing the plurality of hash buckets and the plurality of row buckets.
1 . A database system comprises:
a plurality of computing device clusters, wherein a computing device cluster of the plurality of computing device clusters includes a plurality of computing devices, wherein a computing device of the pluralities of computing devices includes a plurality of computing nodes, wherein a computing node of the pluralities of computing nodes includes a plurality of processing core resources, wherein a set of processing core resources of the pluralities of processing core resources are operable to:
for a query operation of a query regarding data of a dataset:
identify a set of hash values from a plurality of hash values based on a plurality of state information, wherein:
the plurality of hash values is generated based on a hash function of a plurality of rows of the dataset;
a first hash value of the plurality of hash values corresponds to a first row of the plurality of rows;
first state information of the plurality of state information corresponds to the first hash value; and
the set of hash values are to be used in the execution of the query operation instead of using the set of rows of the plurality of rows that the set of hash values represent;
determine whether there is sufficient available memory space to store the set of hash values and a set of corresponding state information of the plurality of state information in allocated main memory for executing the query; and
when there is insufficient available memory space:
partition the set of hash values and the set of corresponding state information into a plurality of groups of hash values based on a hash value distribution protocol; and
store the plurality of groups of hash values in a plurality of non-volatile memory buckets until retrieved to the allocated main memory for execution of the query operation, wherein the plurality of non-volatile memory buckets is distributed within the database system.
2 . The database system of claim 1 , wherein the set of processing core resources comprises:
one or more processing core resources.
3 . The database system of claim 1 , wherein the allocated main memory comprises:
an allocated portion of main memory of computing nodes of the plurality of computing nodes associated with the set of processing core resources.
4 . The database system of claim 1 , wherein the first state information comprises:
data regarding occurrences of when the first hash value has been previously used to execute the query operation instead of using the first row.
5 . The database system of claim 1 , wherein the query operation comprises:
a set operation.
6 . The database system of claim 5 , wherein the set operation comprises one of:
a union distinct operation;
an anti equi-join operation;
an except distinct operation;
an except operation;
a semi equi-join operation;
an intersect distinct operation; or
an intersect all operation.
7 . The database system of claim 1 , wherein the hash value distribution protocol comprises at least one of:
grouping the set of hash values based on selected bits of the hash values to produce a first number of groups; and
when the first number of groups is below a threshold number of groups based on available memory space, grouping the set of hash values based on the selected bits and on further selected bits to produce a second number of groups.
8 . The database system of claim 1 further comprises:
for a second query operation of the query:
identify a second set of hash values from the plurality of hash values based on the plurality of state information, wherein the second set of hash values are to be used in the execution of the second query operation instead of using a second set of rows of the plurality of rows that the second set of hash values represent;
determine whether there is sufficient available memory space to store the second set of hash values and a second set of corresponding state information of the plurality of state information in allocated main memory for executing the query; and
when there is insufficient available memory space:
partition the second set of hash values and the second set of corresponding state information into a second plurality of groups of hash values based on the hash value distribution protocol; and
store the second plurality of groups of hash values in a second plurality of non-volatile memory buckets until retrieved to the allocated main memory for execution of the second query operation, wherein the second plurality of non-volatile memory buckets is distributed within the database system.
9 . A non-transitory computer readable storage medium comprises:
a memory section that stores operational instructions that, when executed by a set of processing core resources of pluralities of processing core resources of a database system, cause the set of processing core resources to:
for a query operation of a query regarding data of a dataset:
identify a set of hash values from a plurality of hash values based on a plurality of state information, wherein:
the plurality of hash values is generated based on a hash function of a plurality of rows of the dataset;
a first hash value of the plurality of hash values corresponds to a first row of the plurality of rows;
first state information of the plurality of state information corresponds to the first hash value; and
the set of hash values are to be used in the execution of the query operation instead of using the set of rows of the plurality of rows that the set of hash values represent;
determine whether there is sufficient available memory space to store the set of hash values and a set of corresponding state information of the plurality of state information in allocated main memory for executing the query; and
when there is insufficient available memory space:
partition the set of hash values and the set of corresponding state information into a plurality of groups of hash values based on a hash value distribution protocol; and
store the plurality of groups of hash values in a plurality of non-volatile memory buckets until retrieved to the allocated main memory for execution of the query operation, wherein the plurality of non-volatile memory buckets is distributed within the database system.
10 . The non-transitory computer readable storage medium of claim 9 , wherein the set of processing core resources comprises:
one or more processing core resources.
11 . The non-transitory computer readable storage medium of claim 9 , wherein the allocated main memory comprises:
an allocated portion of main memory of computing nodes of the plurality of computing nodes associated with the set of processing core resources.
12 . The non-transitory computer readable storage medium of claim 9 , wherein the first state information comprises:
data regarding occurrences of when the first hash value has been previously used to execute the query operation instead of using the first row.
13 . The non-transitory computer readable storage medium of claim 9 , wherein the query operation comprises:
a set operation.
14 . The non-transitory computer readable storage medium of claim 13 , wherein the set operation comprises one of:
a union distinct operation;
an anti equi-join operation;
an except distinct operation;
an except operation;
a semi equi-join operation;
an intersect distinct operation; or
an intersect all operation.
15 . The non-transitory computer readable storage medium of claim 9 , wherein the hash value distribution protocol comprises at least one of:
grouping the set of hash values based on selected bits of the hash values to produce a first number of groups; and
when the first number of groups is below a threshold number of groups based on available memory space, grouping the set of hash values based on the selected bits and on further selected bits to produce a second number of groups.
16 . The non-transitory computer readable storage medium of claim 9 , wherein the instructions further cause the set of processing core resources to:
for a second query operation of the query:
identify a second set of hash values from the plurality of hash values based on the plurality of state information, wherein the second set of hash values are to be used in the execution of the second query operation instead of using a second set of rows of the plurality of rows that the second set of hash values represent;
determine whether there is sufficient available memory space to store the second set of hash values and a second set of corresponding state information of the plurality of state information in allocated main memory for executing the query; and
when there is insufficient available memory space:
partition the second set of hash values and the second set of corresponding state information into a second plurality of groups of hash values based on the hash value distribution protocol; and
store the second plurality of groups of hash values in a second plurality of non-volatile memory buckets until retrieved to the allocated main memory for execution of the second query operation, wherein the second plurality of non-volatile memory buckets is distributed within the database system.