Functionality of decomposition data skew in asymmetric massively parallel processing databases

• US 9,355,127 B2
• Filed: 10/12/2012
• Issued: 05/31/2016
• Est. Priority Date: 10/12/2012
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Stradomski, Tomasz, Gaza, Lukasz, Gruszecki, Artur M., Kazalski, Tomasz, Milka, Grzegorz S., Skibski, Konrad Krzysztof, Yanayt, Natalya A.
• Primary Examiner(s): Morrison, Jay
• Assistant Examiner(s): Hoang, Ken
• Application Number:
  US13/650,863
• Publication Number:
  US 20140108459A1
• Time in Patent Office: 1,327 Days
• Field of Search: 707/602, 707/603, 707/610, 707/635, 707/638, 707/640, 707/646, 707/647, 707/650, 707/670, 707/692, 707/694, 707/696, 707/698, 707/703, 707/713, 707/737, 707/736, 707/747, 707/764, 707/791, 707/792, 707/793, 707/796, 707/825, 707/900.002, 707/900.003, 707/999.101, 707/999.102, 707/999.107
• US Class Current: 1/1
• CPC Class Codes: G06F 16/21 Design, administration or maintenance of databases
• CPC Class Codes: G06F 16/278 Data partitioning, e.g. horizontal or vertical partitioning

Abstract

Database queries are optimized through the functionality of decomposition data skew in an asymmetric massively parallel processing database system. A table having data skew is restructured by (1) storing original data values of a distribution key in a special switch column added to the table, (2) replacing the original data values of the distribution key with modified data values such as randomly generated data values, and (3) partitioning the rows across the nodes of the asymmetric massively parallel processing database system based on the distribution key. The original data values that are stored and replaced may only comprise a subset of the original data values that cause data skew in the table. Data skew is reduced, which improves performance, yet the original data values remain available, which reduces the impact on collocated joins.

Claims

1. A method of restructuring a table having data skew in a computer system, the computer system storing data from a database in partitions on one or more nodes of the computer system, the method comprising:

determining whether original data values of a distribution key column of the table include frequent data values that cause data skew in the table;

after the original data values of the distribution key column have been determined to include the frequent data values, copying only the original data values of the distribution key column that comprise the frequent data values to a switch column added to the table;

after the original data values of the distribution key column that comprise the frequent data values have been copied to the switch column, replacing only the original data values in the distribution key column that comprise the frequent data values with modified data values that reduce the data skew in the table during partitioning, wherein the original data values that are copied and replaced comprise a subset of the original data values and the subset of the original data values comprises one or more of the frequent data values that cause the data skew in the table;

after the original data values in the distribution key column that comprise the frequent data values have been replaced, partitioning the rows of the table across the nodes of the computer system using the distribution key column with the modified data values; and

performing database operations other than the partitioning using the original data values, but not the modified data values.

2. The method of claim 1, wherein the subset causes the data skew in the table.

3. The method of claim 1, wherein the modified data values are randomly generated data values.

4. The method of claim 1, wherein metadata for the table indicates that the original data values of the distribution key are stored in the switch column added to the table.

5. An apparatus for restructuring a table having the data skew, comprising:

a computer system for storing data from a database in partitions on one or more nodes of the computer system; and

a process performed by the computer system, the process configured to;

determine whether original data values of a distribution key column of the table include frequent data values that cause data skew in the table;

after the original data values of the distribution key column have been determined to include the frequent data values, copy only the original data values that comprise the frequent data values of the distribution key column to a switch column added to the table;

after the original data values of the distribution key column have been copied to the switch column, replace only the original data values in the distribution key column that comprise the frequent data values with modified data values that reduce the data skew in the table during partition, wherein the original data values that are copied and replaced comprise a subset of the original data values and the subset of the original data values comprises one or more of the frequent data values that cause the data skew in the table;

after the original data values in the distribution key column that comprise the frequent data values have been replaced, partition the rows of the table across the nodes of the computer system using the distribution key column with the modified data values; and

perform database operations other than the partition using the original data values, but not the modified data values.

6. The apparatus of claim 5, wherein the subset causes the data skew in the table.

7. The apparatus of claim 5, wherein the modified data values are randomly generated data values.

8. The apparatus of claim 5, wherein metadata for the table indicates that the original data values of the distribution key are stored in the switch column added to the table.

9. An article of manufacture comprising a computer readable storage medium encoded with computer program instructions which, when accessed by a computer system storing data from a database in partitions on one or more nodes of the computer system, cause the computer system to operate as a specially programmed computer system, executing a method for restructuring a table having the data skew, the method comprising:

determining whether original data values of a distribution key column of the table include frequent data values that cause data skew in the table;

after the original data values of the distribution key column have been determined to include the frequent data values, copying only the original data values of the distribution key column that comprise the frequent data values to a switch column added to the table;

after the original data values of the distribution key column have been copied to the switch column, replacing only the original data values in the distribution key column that comprise the frequent data values with modified data values that reduce the data skew in the table during partitioning, wherein the original data values that are copied and replaced comprise a subset of the original data values and the subset of the original data values comprises one or more of the frequent data values that cause the data skew in the table;

after the original data values in the distribution key column that comprise the frequent data values have been replaced, partitioning the rows of the table across the nodes of the computer system using the distribution key column with the modified data values; and

performing database operations other than the partitioning using the original data values, but not the modified data values.

10. The article of claim 9, wherein the subset causes the data skew in the table.

11. The article of claim 9, wherein the modified data values are randomly generated data values.

12. The article of claim 9, wherein metadata for the table indicates that the original data values of the distribution key are stored in the switch column added to the table.