Group-by efficiency though functional dependencies and non-blocking aggregation functions

Patent metadata

Field	Value
Publication number	US-11314736-B2
Application number	US-202016744635-A
Country	US
Kind code	B2
Filing date	Jan 16, 2020
Priority date	Jan 16, 2020
Publication date	Apr 26, 2022
Grant date	Apr 26, 2022

Abstract

Techniques described herein proposes a Dependent Grouping Column Rewrite that simplifies a grouping operation by excluding dependent grouping columns from a group by clause. A non-blocking aggregation function is applied to dependent grouping columns in at least select clauses. Such a simplified grouping operation reduces memory pressure, speeds up grouping operations, and saves compute time, as grouping columns that have no additional effect on the grouping operation are excluded from processing (e.g., hashing and key matching).

First claim

What is claimed is:

1. A method comprising:
rewriting a particular query;
wherein the particular query references a set of grouping columns;
wherein rewriting the particular query comprises:
determining that rewrite criteria are satisfied, wherein the rewrite criteria includes:
the set of grouping columns includes a set of one or more dependent grouping columns and at least one grouping column that the set of one or more dependent grouping columns does not contain;
and wherein each dependent grouping column from the set of one or more dependent grouping columns is functionally dependent on one or more grouping columns from the set of grouping columns;

in response to determining that the rewrite criteria are satisfied, generating a rewritten query that:
excludes the set of one or more dependent grouping columns as grouping columns;
for each column of the set of one or more dependent grouping columns, projects an aggregation function that is applied to a respective column.

2. The method of claim 1 , wherein the aggregation function is a non-blocking aggregation function.

3. The method of claim 2 , wherein the non-blocking aggregation function is a deterministic function.

4. The method of claim 1 , wherein generating the rewrite query comprises removing the set of one or more dependent grouping columns from a grouping clause.

5. The method of claim 1 , wherein the at least one grouping column that the set of one or more dependent grouping columns does not contain includes a primary key column.

6. The method of claim 1 , wherein generating the rewrite query comprises including the aggregation function in a select clause.

7. The method of claim 1 , wherein the aggregation function is a FIRST aggregation function.

8. The method of claim 1 , wherein generating the rewrite query comprises including the aggregation function in an ordering clause.

9. The method of claim 1 , wherein:
the at least one grouping column that the set of one or more dependent grouping columns does not contain is a single column;
the set of one or more dependent grouping columns contains multiple columns.

10. One or more non-transitory computer-readable storage medium storing instructions that, when executed by one or more processors, cause the one or more processors to perform functions comprising:
rewriting a particular query;
wherein the particular query references a set of grouping columns;
wherein rewriting the particular query comprises:
determining that rewrite criteria are satisfied, wherein the rewrite criteria includes:
the set of grouping columns includes a set of one or more dependent grouping columns and at least one grouping column that the set of one or more dependent grouping columns does not contain;
and wherein each dependent grouping column from the set of one or more dependent grouping columns is functionally dependent on one or more grouping columns from the set of grouping columns;

in response to determining that the rewrite criteria are satisfied, generating a rewritten query that:
excludes the set of one or more dependent grouping columns as grouping columns;
for each column of the set of one or more dependent grouping columns, projects an aggregation function that is applied to a respective column.

11. The one or more non-transitory computer-readable storage medium of claim 10 , wherein the aggregation function is a non-blocking aggregation function.

12. The one or more non-transitory computer-readable storage medium of claim 11 , wherein the non-blocking aggregation function is a deterministic function.

13. The one or more non-transitory computer-readable storage medium of claim 10 , wherein generating the rewrite query comprises removing the set of one or more dependent grouping columns from a grouping clause.

14. The one or more non-transitory computer-readable storage medium of claim 10 , wherein the at least one grouping column that the set of one or more dependent grouping columns does not contain includes a primary key column.

15. The one or more non-transitory computer-readable storage medium of claim 10 , wherein generating the rewrite query comprises including the aggregation function in a select clause.

16. The one or more non-transitory computer-readable storage medium of claim 10 , wherein the aggregation function is a FIRST aggregation function.

17. The one or more non-transitory computer-readable storage medium of claim 10 , wherein generating the rewrite query comprises including the aggregation function in an ordering clause.

18. The one or more non-transitory computer-readable storage medium of claim 10 , wherein:
the at least one grouping column that the set of one or more dependent grouping columns does not contain is a single column;
the set of one or more dependent grouping columns contains multiple columns.

Assignees

• Oracle Int Corp

Inventors

• Fender Pit
• Schlegel Benjamin
• Brantner Matthias

Classifications

• G06F16/221 Primary

  Column-oriented storage; Management thereof · CPC title

• G06F16/24534

  Query rewriting; Transformation · CPC title

• G06F16/2458

  Special types of queries, e.g. statistical queries, fuzzy queries or distributed queries · CPC title

• G06F16/244 Primary

  Grouping and aggregation · CPC title