Method for rebalancing data partitions

Abstract

Embodiments of the present invention disclose a method, computer program product, and system for rebalancing partitioned data based, at least in part, on one or more characteristics of the plurality of database partitions. The computer updating characteristics, wherein the one or more characteristics include one or both of a) an average number of records per partition, and b) an average number of unique limit key values per partition. Embodiments of the present invention disclose a method, computer program product, and system for limit key extrapolation in a database. Responsive to a determination that the upper limit key value of the last loaded record is greater than the first upper limit key value of the first empty partition, the computer redefines the first upper limit key value of the first empty partition by an extrapolated upper limit key value based, at least in part, on the range of limit key values.

What is claimed is: 
     
       1. A method for rebalancing partitioned data in a database, the method comprising:
 a computer initiating a rebalance of a set of data records included in a plurality of database partitions by unloading the set of data records, wherein the rebalance of the set of data records is improved based, at least in part, on one or more characteristics of the plurality of database partitions that are determined during the rebalance of the set of data records; 
 the computer updating the one or more characteristics during the rebalancing of the set of data records, wherein the one or more characteristics include one or both of a) an average number of records per partition, and b) an average number of unique limit key values per partition; 
 the computer loading the set of data records into the plurality of database partitions based, at least in part, on the one or more characteristics, wherein the one or more characteristics are used to direct the rebalancing of the set of data records; 
 the computer determining if a first empty partition remains after loading the set of data records; and 
 responsive to determining that a first empty partition remains after loading the set of data records, the computer performing limit key extrapolation for the first empty partition by:
 the computer defining at least one database partition of the plurality of database partitions by an upper limit key value based on a limit key value of a last loaded data record; 
 the computer determining a range of limit key values for the plurality of database partitions from limit key definitions in a database; 
 the computer determining if the limit key of the last loaded record is greater than a first upper limit key value of the first empty partition, wherein the first upper limit key value of the first empty partition was previously defined by the upper limit key value of a corresponding partition before a rebalance; 
 responsive to a determination that the upper limit key value of the last loaded record is greater than the first upper limit key value of the first empty partition, the computer redefining the first upper limit key value of the first empty partition by an extrapolated upper limit key value based, at least in part, on the range of limit key values; 
 the computer calculating a remaining range of limit key values by subtracting a greatest limit key value of a reloaded set of data records from a greatest limit key value in the range of limit key values for the plurality of database partitions; 
 the computer calculating an arithmetic average by dividing the remaining range of limit key values by a number of empty partitions; and 
 the computer calculating the first upper limit key value of the first empty partition by adding the calculated arithmetic average to the upper limit key value of the last loaded record. 
 
 
     
     
       2. The method of  claim 1 , the method further comprising:
 the computer determining a grouping comprising a sub-set of data records in the set of data records with a single limit key value; 
 the computer calculating the average number of records per partition by dividing a total number of data records in the set of data records by a total number of database partitions in the plurality of database partitions; 
 the computer determining a partition is filled based, at least in part, on a loaded grouping increasing the number of data records loaded into a first partition, such that the number of data records loaded into the first partition is at least equal to the average number of records per partition; and 
 responsive to a determination that a partition is filled, the computer updating the average number of records per partition, wherein the updated average number of records per partition is determined by dividing a number of data records remaining to be loaded by a number of database partitions remaining to be filled. 
 
     
     
       3. The method of  claim 1 , the method further comprising:
 the computer determining a grouping comprising a sub-set of data records in the set of data records with a single limit key value; 
 the computer determining a total number of unique limit key values included in the set of data records; 
 the computer calculating the average number of unique limit key values per partition by dividing the total number of unique limit key values in the set of data records by a total number of database partitions in the plurality of database partitions; 
 the computer determining a partition is filled based, at least in part, on a loaded grouping increasing the number of unique limit key values loaded into a first partition, such that the number of unique limit key values loaded into the first partition is at least equal to the average number of unique limit key values per partition; and 
 responsive to a determination that a partition is filled, the computer updating the average number of unique limit key values per partition, wherein the updated average number of unique limit key values per partition is determined by dividing a number of unique limit key values remaining to be loaded by a number of database partitions remaining to be filled. 
 
     
     
       4. The method of  claim 3 , wherein the computer determining the total number of unique limit key values comprises:
 the computer unloading the set of data records using one or more of: a) an index scan of a unique index of the plurality of database partitions, b) a table space scan, and c) a sort of the set of data records by ascending limit key value; and 
 the computer counting the total number of unique limit key values using one of: a) a counter using an index key during unloading, b) a hashing algorithm during unloading, and c) a counter during re-reading of the set of data records subsequent to unloading and sorting by ascending limit key values. 
 
     
     
       5. The method of  claim 1 , wherein the step of the computer calculating an arithmetic average by dividing the remaining range of limit key values by the number of empty partitions comprises:
 the computer determining the arithmetic average based, at least in part, on a limit key column data type, wherein the limit key column data type is one of:
 a) a numeric limit key column data type, wherein the arithmetic average is determined by dividing the remaining range of limit key values by the number of empty partitions; 
 b) a date/timestamp limit key column data type, wherein the arithmetic average is determined by converting the remaining range of limit key values from date/timestamp values into a number of days, and then dividing by the number of empty partitions; or 
 c) a character limit key column data type, wherein, the arithmetic average is determined by converting the remaining range of limit key values from character values to floating point values, dividing by the number of empty partitions, and then converting a resulting floating point value to a character value. 
 
 
     
     
       6. The method of  claim 1 , the method further comprising:
 the computer calculating a second upper limit key value of a second empty partition by adding the calculated arithmetic average to the first upper limit key value of the first empty partition. 
 
     
     
       7. A computer program product for rebalancing partitioned data in a database, the computer program product comprising:
 a computer readable storage medium and program instructions stored on the computer readable storage medium, the program instructions comprising:
 program instructions to initiate a rebalance of a set of data records included in a plurality of database partitions by unloading the set of data records, wherein the rebalance of the set of data records is improved based, at least in part, on one or more characteristics of the plurality of database partitions that are determined during the rebalance of the set of data records; 
 program instructions to update the one or more characteristics during the rebalancing of the set of data records, wherein the one or more characteristics include one or both of a) an average number of records per partition, and b) an average number of unique limit key values per partition; 
 program instructions to load the set of data records into the plurality of database partitions based, at least in part, on the one or more characteristics, wherein the one or more characteristics are used to direct the rebalancing of the set of data records; 
 program instructions to determine if a first empty partition remains after loading the set of data records; and 
 responsive to determining that a first empty partition remains after loading the set of data records, program instructions to perform limit key extrapolation for the first empty partition by executing:
 program instructions to define at least one database partition of the plurality of database partitions by an upper limit key value based on a limit key value of a last loaded data record; 
 program instructions to determine a range of limit key values for the plurality of database partitions from limit key definitions in a database; 
 program instructions to determine if the limit key of the last loaded record is greater than a first upper limit key value of the first empty partition, wherein the first upper limit key value of the first empty partition was previously defined by the upper limit key value of a corresponding partition before a rebalance; 
 responsive to a determination that the upper limit key value of the last loaded record is greater than the first upper limit key value of the first empty partition, program instructions to redefine the first upper limit key value of the first empty partition by an extrapolated upper limit key value based, at least in part, on the range of limit key values; 
 program instructions to calculate a remaining range of limit key values by subtracting a greatest limit key value of a reloaded set of data records from a greatest limit key value in the range of limit key values for the plurality of database partitions; 
 program instructions to calculate an arithmetic average by dividing the remaining range of limit key values by a number of empty partitions; and 
 program instructions to calculate the first upper limit key value of the first empty partition by adding the calculated arithmetic average to the upper limit key value of the last loaded record. 
 
 
 
     
     
       8. The computer program product of  claim 7 , the computer program product further comprising:
 program instructions to determine a grouping comprising a sub-set of data records in the set of data records with a single limit key value; 
 program instructions to calculate the average number of records per partition by dividing a total number of data records in the set of data records by a total number of database partitions in the plurality of database partitions; 
 program instructions to determine a partition is filled based, at least in part, on a loaded grouping increasing the number of data records loaded into a first partition, such that the number of data records loaded into the first partition is at least equal to the average number of records per partition; and 
 responsive to a determination that a partition is filled, program instructions to update the average number of records per partition, wherein the updated average number of records per partition is determined by dividing a number of data records remaining to be loaded by a number of database partitions remaining to be filled. 
 
     
     
       9. The computer program product of  claim 7 , the computer program product further comprising:
 program instructions to determine a grouping comprising a sub-set of data records in the set of data records with a single limit key value; 
 program instructions to determine a total number of unique limit key values included in the set of data records; 
 program instructions to calculate the average number of unique limit key values per partition by dividing the total number of unique limit key values in the set of data records by a total number of database partitions in the plurality of database partitions; 
 program instructions to determine a partition is filled based, at least in part, on a loaded grouping increasing the number of unique limit key values loaded into a first partition, such that the number of unique limit key values loaded into the first partition is at least equal to the average number of unique limit key values per partition; and 
 responsive to a determination that a partition is filled, program instructions to update the average number of unique limit key values per partition, wherein the updated average number of unique limit key values per partition is determined by dividing a number of unique limit key values remaining to be loaded by a number of database partitions remaining to be filled. 
 
     
     
       10. The computer program product of  claim 9 , wherein program instructions to determine the total number of unique limit key values comprises:
 program instructions to unload the set of data records using one or more of: a) an index scan of a unique index of the plurality of database partitions, b) a table space scan, and c) a sort of the set of data records by ascending limit key value; and 
 program instructions to count the total number of unique limit key values using one of: a) a counter using an index key during unloading, b) a hashing algorithm during unloading, and c) a counter during re-reading of the set of data records subsequent to unloading and sorting by ascending limit key values. 
 
     
     
       11. The computer program product of  claim 7 , wherein the program instructions to calculate an arithmetic average by dividing the remaining range of limit key values by the number of empty partitions comprise:
 program instructions to determine the arithmetic average based, at least in part, on a limit key column data type, wherein the limit key column data type is one of:
 a) a numeric limit key column data type, wherein the arithmetic average is determined by dividing the remaining range of limit key values by the number of empty partitions; 
 b) a date/timestamp limit key column data type, wherein the arithmetic average is determined by converting the remaining range of limit key values from date/timestamp values into a number of days, and then dividing by the number of empty partitions; or 
 c) a character limit key column data type, wherein, the arithmetic average is determined by converting the remaining range of limit key values from character values to floating point values, dividing by the number of empty partitions, and then converting a resulting floating point value to a character value. 
 
 
     
     
       12. The computer program product of  claim 7 , the computer program product further comprising:
 program instructions to calculate a second upper limit key value of a second empty partition by adding the calculated arithmetic average to the first upper limit key value of the first empty partition. 
 
     
     
       13. A computer system for rebalancing partitioned data in a database, the computer system comprising:
 one or more computer processors; 
 one or more computer readable storage media; 
 program instructions stored on the computer readable storage media for execution by at least one of the one or more processors, the program instructions comprising:
 program instructions to initiate a rebalance of a set of data records included in a plurality of database partitions by unloading the set of data records, wherein the rebalance of the set of data records is improved based, at least in part, on one or more characteristics of the plurality of database partitions that are determined during the rebalance of the set of data records; 
 program instructions to update the one or more characteristics during the rebalancing of the set of data records, wherein the one or more characteristics include one or both of a) an average number of records per partition, and b) an average number of unique limit key values per partition; 
 program instructions to load the set of data records into the plurality of database partitions based, at least in part, on the one or more characteristics, wherein the one or more characteristics are used to direct the rebalancing of the set of data records; 
 program instructions to determine if a first empty partition remains after loading the set of data records; and 
 responsive to determining that a first empty partition remains after loading the set of data records, program instructions to perform limit key extrapolation for the first empty partition by executing:
 program instructions to define at least one database partition of the plurality of database partitions by an upper limit key value based on a limit key value of a last loaded data record; 
 program instructions to determine a range of limit key values for the plurality of database partitions from limit key definitions in a database; 
 program instructions to determine if the limit key of the last loaded record is greater than a first upper limit key value of the first empty partition, wherein the first upper limit key value of the first empty partition was previously defined by the upper limit key value of a corresponding partition before a rebalance; 
 responsive to a determination that the upper limit key value of the last loaded record is greater than the first upper limit key value of the first empty partition, program instructions to redefine the first upper limit key value of the first empty partition by an extrapolated upper limit key value based, at least in part, on the range of limit key values; 
 program instructions to calculate a remaining range of limit key values by subtracting a greatest limit key value of a reloaded set of data records from a greatest limit key value in the range of limit key values for the plurality of database partitions; 
 program instructions to calculate an arithmetic average by dividing the remaining range of limit key values by a number of empty partitions; and 
 program instructions to calculate the first upper limit key value of the first empty partition by adding the calculated arithmetic average to the upper limit key value of the last loaded record. 
 
 
 
     
     
       14. The computer system of  claim 13 , the computer system further comprising:
 program instructions to determine a grouping comprising a sub-set of data records in the set of data records with a single limit key value; 
 program instructions to calculate the average number of records per partition by dividing a total number of data records in the set of data records by a total number of database partitions in the plurality of database partitions; 
 program instructions to determine a partition is filled based, at least in part, on a loaded grouping increasing the number of data records loaded into a first partition, such that the number of data records loaded into the first partition is at least equal to the average number of records per partition; and 
 responsive to a determination that a partition is filled, program instructions to update the average number of records per partition, wherein the updated average number of records per partition is determined by dividing a number of data records remaining to be loaded by a number of database partitions remaining to be filled. 
 
     
     
       15. The computer system of  claim 13 , the computer system further comprising:
 program instructions to unload the set of data records using one or more of: a) an index scan of a unique index of the plurality of database partitions, b) a table space scan, and c) a sort of the set of data records by ascending limit key value; 
 program instructions to count a total number of unique limit key values in the set of data records using one of: a) a counter using an index key during unloading, b) a hashing algorithm during unloading, and c) a counter during re-reading of the set of data records subsequent to unloading and sorting by ascending limit key values; 
 program instructions to determine a grouping comprising a sub-set of data records in the set of data records with a single limit key value; 
 program instructions to calculate the average number of unique limit key values per partition by dividing the total number of unique limit key values in the set of data records by a total number of database partitions in the plurality of database partitions; 
 program instructions to determine a partition is filled based, at least in part, on a loaded grouping increasing the number of unique limit key values loaded into a first partition, such that the number of unique limit key values loaded into the first partition is at least equal to the average number of unique limit key values per partition; and 
 responsive to a determination that a partition is filled, program instructions to update the average number of unique limit key values per partition, wherein the updated average number of unique limit key values per partition is determined by dividing a number of unique limit key values remaining to be loaded by a number of database partitions remaining to be filled. 
 
     
     
       16. The computer system of  claim 13 , the computer system further comprising:
 program instructions to calculate a second upper limit key value of a second empty partition by adding the calculated arithmetic average to the first upper limit key value of the first empty partition. 
 
     
     
       17. The computer program system of  claim 13 , the program instructions further comprising:
 program instructions to determine a grouping comprising a sub-set of data records in the set of data records with a single limit key value; 
 program instructions to determine a total number of unique limit key values included in the set of data records; 
 program instructions to calculate the average number of unique limit key values per partition by dividing the total number of unique limit key values in the set of data records by a total number of database partitions in the plurality of database partitions; 
 program instructions to determine a partition is filled based, at least in part, on a loaded grouping increasing the number of unique limit key values loaded into a first partition, such that the number of unique limit key values loaded into the first partition is at least equal to the average number of unique limit key values per partition; and 
 responsive to a determination that a partition is filled, program instructions to update the average number of unique limit key values per partition, wherein the updated average number of unique limit key values per partition is determined by dividing a number of unique limit key values remaining to be loaded by a number of database partitions remaining to be filled. 
 
     
     
       18. The computer program product of  claim 13 , wherein the program instructions to calculate an arithmetic average by dividing the remaining range of limit key values by the number of empty partitions comprise:
 program instructions to determine the arithmetic average based, at least in part, on a limit key column data type, wherein the limit key column data type is one of:
 a) a numeric limit key column data type, wherein the arithmetic average is determined by dividing the remaining range of limit key values by the number of empty partitions; 
 b) a date/timestamp limit key column data type, wherein the arithmetic average is determined by converting the remaining range of limit key values from date/timestamp values into a number of days, and then dividing by the number of empty partitions; or 
 c) a character limit key column data type, wherein, the arithmetic average is determined by converting the remaining range of limit key values from character values to floating point values, dividing by the number of empty partitions, and then converting a resulting floating point value to a character value.