Data-Aware Scalable Parallel Execution of Rollup Operations

• US 20140214800A1
• Filed: 01/30/2013
• Published: 07/31/2014
• Est. Priority Date: 01/30/2013
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Bellamkonda, Srikanth, Liang, Jianhua
• Granted Patent: US 9,235,621 B2
• Time in Patent Office: Days
• US Class Current: 707/718
• CPC Class Codes: G06F 16/24532 of parallel queries
• CPC Class Codes: G06F 16/24553 of query operations

Abstract

According to one aspect of the invention, for a database statement that specifies rollup operations, a data distribution key may be selected among a plurality of candidate keys. Numbers of distinct values of the candidate keys may be monitored with respect to a particular set of rows. Hash values may also be generated by column values in the candidate keys. The data distribution key may be determined based on results of monitoring the numbers of distinct values of the candidate keys as well as the frequencies of hash values computed based on column values of the candidate keys. Rollup operations may be shared between different stages of parallel executing processes and data may be distributed between the different stages of parallel executing processes based on the selected data distribution key.

Claims

1. A method comprising:

monitoring a plurality of numbers of distinct values (NDVs) for a plurality of candidate distribution keys, wherein each candidate distribution key of said plurality of candidate distribution keys comprises one or more group-by keys of an ordered list of group-by columns specified by a database statement that requests performing one or more rollup operations relating to the ordered list of group-by keys;

selecting a distribution key from said plurality of candidate distribution keys based at least in part on results of monitoring the plurality of NDVs;

distributing a set of rows, based at least in part on the selected distribution key, between first parallel executing processes and second parallel executing processes, wherein the one or more rollup operations are performed by the first parallel executing processes and the second parallel executing processes against the set of rows;

wherein the method is performed by one or more computing devices.

2. The method of claim 1, wherein the first parallel executing processes are a plurality of rollup distributors, and wherein the second parallel executing processes are a plurality of rollup collectors.

3. The method of claim 1, wherein said selected distribution key comprises at least one rollup column and zero or more non-rollup columns in said ordered list of group-by columns, wherein the first parallel executing processes perform one or more of the rollup operations corresponding to one or more subsets of columns up to all columns of said selected distribution key, and wherein the second parallel executing processes perform any remaining rollup operations of the rollup operations.

4. The method of claim 1, wherein said set of rows is compressed by group-by operations before being sent to the first parallel executing processes.

5. The method of claim 1, further comprising:

generating an execution plan for said database statement issued against said set of rows; and

determining the number of the first parallel executing processes and the number of the second parallel executing processes to be included in said execution plan.

6. The method of claim 1, further comprising:

monitoring frequencies of distinct values of a hash function, wherein a value of said hash function is computed based on values of columns in said plurality of candidate data distribution keys;

selecting said distribution key based at least in part on results of monitoring the frequencies of the distinct hash function values.

7. The method of claim 6, wherein said distinct values of said hash function as computed by values of columns in said selected distribution key are used to distribute said set of rows between the first parallel executing processes and the second parallel executing processes.

8. The method of claim 1, wherein monitoring said plurality of NDVs for said plurality of candidate distribution keys is individually performed by each of the first parallel executing processes based on a respective subset of rows in said set of rows.

9. The method of claim 1, wherein said selected distribution key is selected from a plurality of locally selected distribution keys, and wherein each locally selected distribution keys is selected by a parallel executing process of said first parallel executing processes based on a respective subset of rows in said set of rows.

10. The method of claim 1, wherein said rollup operations produce one or more of subtotals or a grand total on a measure column.

11. One or more non-transitory computer-readable media storing instructions which, when executed by one or more processors, cause performance of a method for evaluating reporting window functions, the method comprising:

monitoring a plurality of numbers of distinct values (NDVs) for a plurality of candidate distribution keys, wherein each candidate distribution key of said plurality of candidate distribution keys comprises one or more group-by keys of an ordered list of group-by columns specified by a database statement that requests performing one or more rollup operations relating to the ordered list of group-by keys;

selecting a distribution key from said plurality of candidate distribution keys based at least in part on results of monitoring the plurality of NDVs;

distributing a set of rows, based at least in part on the selected distribution key, between first parallel executing processes and second parallel executing processes, wherein the one or one rollup operations are performed by the first parallel executing processes and the second parallel executing processes against the set of rows.

12. The one or more media of claim 11, wherein the first parallel executing processes are a plurality of rollup distributors, and wherein the second parallel executing processes are a plurality of rollup collectors.

13. The one or more media of claim 11, wherein said selected distribution key comprises at least one rollup column and zero or more non-rollup columns in said ordered list of group-by columns, wherein the first parallel executing processes perform one or more of the rollup operations corresponding to one or more subsets of columns up to all columns of said selected distribution key, and wherein the second parallel executing processes perform any remaining rollup operations of the rollup operations.

14. The one or more media of claim 11, wherein said set of rows is compressed by group-by operations before being sent to the first parallel executing processes.

15. The one or more media of claim 11, wherein the method further comprises:

generating an execution plan for said database statement issued against said set of rows; and

determining the number of the first parallel executing processes and the number of the second parallel executing processes to be included in said execution plan.

16. The one or more media of claim 11, further comprising:

monitoring frequencies of distinct values of a hash function, wherein a value of said hash function is computed based on values of columns in said plurality of candidate data distribution keys;

selecting said distribution key based at least in part on results of monitoring the frequencies of the distinct hash function values.

17. The one or more media of claim 16, wherein said distinct values of said hash function as computed by values of columns in said selected distribution key are used to distribute said set of rows between the first parallel executing processes and the second parallel executing processes.

18. The one or more media of claim 11, wherein monitoring said plurality of NDVs for said plurality of candidate distribution keys is individually performed by each of the first parallel executing processes based on a respective subset of rows in said set of rows.

19. The one or more media of claim 11, wherein said selected distribution key is selected from a plurality of locally selected distribution keys, and wherein each locally selected distribution keys is selected by a parallel executing process of said first parallel executing processes based on a respective subset of rows in said set of rows.

20. The one or more media of claim 11, wherein said rollup operations produce one or more of subtotals or a grand total on a measure column.