Balancing data across partitions of a table space during load processing
A balancing technique allows a database administrator to perform a mass data load into a relational database employing partitioned tablespaces. The technique automatically balances the usage of the partitions in a tablespace as the data is loaded. Previous definitions of the partitions are modified after the loading of the data into the tablespace to conform with the data loaded into the tablespace.
1. A method comprising:
receiving a request to load data into a plurality of partitions of a table of a relational database of a computer system;
reading a plurality of data records, each data record having a key value;
loading the plurality of data records evenly across the plurality of partitions by:
sorting the plurality of data records by key value,
after the sorting, determining a quantity of records of the plurality of data records to be loaded into each partition of the plurality of partitions by dividing a count of the plurality of data records by a count of the plurality of partitions, and
writing the plurality of data records into the plurality of partitions, without regard to currently defined key value limits for the plurality of partitions, so that each partition includes the quantity of data records; and
in response to loading the plurality of data records, modifying the currently defined key value limits for the plurality of partitions by:
selecting the partition with a highest partition number,
setting the key value limit for the selected partition, comprising:
setting the key value limit for the selected partition to a highest key value stored in the selected partition, and
selecting the partition with a next lower partition number, and
repeating the act of setting the key value limit for the selected partition for all remaining partitions of the plurality of partitions.
2. The method of claim 1 , further comprising counting the plurality of data records as they are read.
3. A method comprising
receiving a request to load data into a plurality of partitions of a table of a relational database of a computer system;
reading a plurality of data records, each data record having a key value;
loading the plurality of data records evenly across the plurality of partitions by:
sorting the plurality of data records by key value,
after the sorting, determining a quantity of records of the plurality of data records to be loaded into each partition of the plurality of partitions, and
writing the plurality of data records into the plurality of partitions, without regard to currently defined key value limits for the plurality of partitions, so that each partition includes the quantity of data records; and
in response to loading the plurality of data records, modifying the currently defined key value limits for the plurality of partitions by:
selecting a partition of the plurality of partitions,
setting the key value limit of the selected partition using a lowest key value stored in an adjacent partition to the selected partition,
selecting a previously unselected partition of the plurality of partitions, and
repeating the act of setting the key value limit of the selected partition and the act of selecting an unselected partition of the plurality of partitions for all remaining partitions of the plurality of partitions so that a pre-existing order of the key value limits is preserved.
4. The method of claim 3 , wherein the act of modifying the currently defined key value limits further includes:
for a partition with a highest partition number, setting the key value limit to a highest key value of a data record stored in the partition with the highest partition number.
5. The method of claim 3 , wherein setting the key value limit of the selected partition using the lowest key value stored in the next partition comprises:
setting the key value limit to a predecessor value of the lowest key value of a data record stored in the adjacent partition of the plurality of partitions.
6. The method of claim of claim 3 , wherein the act of setting the key value limit for the selected partition comprises:
setting the key value limit to a value between a highest key value of a data record stored in the selected partition and a lowest key value of a data record stored in the adjacent partition of the plurality of partitions.
7. A non-transitory computer readable storage device with instructions for a computer system stored thereon wherein the instructions cause the computer system to perform the method of claim 1 .
8. A networked computer system comprising:
a plurality of computers communicatively coupled, at least one of the plurality of computers programmed to perform at least a portion of the method of claim 1 wherein the entire method of claim 1 is performed collectively by the plurality of computers.
9. A computer system, comprising:
a processor;
a first datastore;
a database, stored in the first datastore, comprising:
a tablespace, comprising:
a plurality of partitions configured to store data records, each data record having a key value; and
a plurality of key value limits, each associated with a partition of the plurality of partitions, wherein the key values of the data records stored in each partition of the plurality of partitions are no greater than the associated key value limit;
a data record source, operatively coupled to the processor; and
a program datastore, operatively coupled to the processor, configured to hold programs that when executed, cause the processor to perform acts comprising:
receiving a request to load data into the plurality of partitions;
reading a plurality of data records from the data record source, each data record having a key value;
sorting the plurality of data records by key value,
after the sorting, determining a quantity of records of the plurality of data records to be loaded into each partition of the plurality of partitions by dividing a count of the plurality of data records by a count of the plurality of partitions, and
writing the data records into the plurality of partitions, without regard to currently defined key value limits for the plurality of partitions, so that each partition includes the quantity of data records; and
in response to writing the plurality of data records into the plurality of partitions, modifying the currently defined key value limits by:
selecting a partition of the plurality of partitions,
setting the key value limit for the selected partition, preserving a pre-existing order of the key value limits of the plurality of partitions,
selecting a previously unselected partition of the plurality of partitions, and
repeating the act of setting the key value limit for the selected partition and selecting another previously unselected partition for all remaining partitions of the plurality of partitions.
10. The computer system of claim 9 , wherein setting the key value limit of the selected partition comprises:
determining a highest key value of a data record distributed into the selected partition of the plurality of partitions; and
setting the key value limit for the selected partition to the highest key value.
11. The computer system of claim 9 , wherein setting the key value limit of the selected partition comprises:
determining a lowest key value of a data record distributed into an adjacent partition of the selected partition; and
setting the key value limit for the selected partition of the plurality of partitions based on the lowest key value.
12. The computer system of claim 11 , wherein setting the key value limit of the selected partition comprises:
setting the key value limit to a predecessor value of the lowest key value.
13. The computer system of claim 11 , wherein setting the key value limit for the selected partition comprises:
setting the key value limit to a value between a highest key value of a data record stored in the selected partition and the lowest key value of the data record stored in the adjacent partition.
14. The computer system of claim 11 , wherein modifying the key value limits for each partition further includes:
for a last partition, setting the key value limit to a highest key value of a data record stored in the last partition.
15. The computer system of claim 11 , wherein the key values are monotonically decreasing and the adjacent partition is a predecessor partition.
16. The computer system of claim 9 , wherein reading the plurality of data records includes counting the plurality of data records as they are read.
17. A non-transitory computer readable storage device with instructions for a computer system stored thereon wherein the instructions cause the computer system to perform the method of claim 3 .
18. The method of claim of claim 3 , wherein the key values of the data records are monotonically decreasing and the adjacent partition is a predecessor partition.
19. The method of claim of claim 1 , further comprising:
determining whether a difference between a first updated limit key of a first partition and a second updated limit key of a successor partition is less than a predetermined value; and
rolling back the writing of the records when the difference is less than the predetermined value, so that the database is restored to a state prior to loading the plurality of data records.