Driving massive scale out through rewrites of analytical functions

Patent metadata

Field	Value
Publication number	US-11455303-B2
Application number	US-201916516898-A
Country	US
Kind code	B2
Filing date	Jul 19, 2019
Priority date	Jul 19, 2019
Publication date	Sep 27, 2022
Grant date	Sep 27, 2022

Abstract

According to an embodiment, a method includes rewriting a particular query to generate a rewritten query. The particular query specifies a window function operator, a particular input to the window function operator, and an analytical function. Rewriting the particular query includes assigning the particular input to an intermediate relation and replacing the window function operator with a replacement operator. The method further includes assigning to the replacement operator an aggregate function corresponding to the analytical function, and the intermediate relation. In this embodiment, the method also includes placing a join operator that joins the intermediate relation with an output of the replacement operator.

First claim

What is claimed is:

1. A method, comprising:
a DBMS (database management system) rewriting a particular query to generate a rewritten query, wherein the particular query specifies a window function operator, a particular input to the window function operator, one or more PARTITION BY attributes, and an analytical function, wherein rewriting the particular query includes:
assigning the particular input to an intermediate relation;
replacing the window function operator with a GROUP BY operator, and assigning to the GROUP BY operator:
the one or more PARTITION BY attributes as grouping attributes,
an aggregate function corresponding to the analytical function, and
the intermediate relation;

placing a join operator that joins the intermediate relation with an output of the GROUP BY operator, wherein the join operator includes the one or more PARTITION BY attributes as one or more join attributes; and

said DBMS executing said rewritten query.

2. The method of claim 1 , wherein rewriting the particular query is performed in response to determining that the window function operator satisfies criteria, wherein the criteria include that the window function operator applies no framing, has no ORDER BY clause, and includes a PARTITION BY clause.

3. The method of claim 1 , wherein the one or more join attributes are constructed by matching each of the one or more PARTITION BY attributes between the intermediate relation and the GROUP BY operator.

4. The method of claim 1 , wherein the one or more join attributes are constructed by matching each of the one or more PARTITION BY attributes between the intermediate relation and the GROUP BY operator, and concatenating each of a plurality of join attributes together.

5. The method of claim 1 , wherein the GROUP BY operator and the join operator share the intermediate relation as an input.

6. The method of claim 1 , wherein the intermediate relation is materialized as a temporary result, and the GROUP BY operator and the join operator, when executed, read from the temporary result.

7. The method of claim 1 , further comprising executing the rewritten query on a dataset to obtain a result.

8. A method, comprising:
a DBMS (database management system) rewriting a particular query to generate a rewritten query, wherein the particular query specifies a window function operator, a particular input to the window function operator, and an analytical function, wherein rewriting the particular query includes:
assigning the particular input to an intermediate relation;
replacing the window function operator with a pure aggregation operator and assigning to the pure aggregation operator:
an aggregate function corresponding to the analytical function, and
the intermediate relation;

placing a Cartesian product operator that operates on the intermediate relation and on an output of the pure aggregation operator; and

said DBMS executing said rewritten query.

9. The method of claim 8 , wherein rewriting the particular query is performed in response to determining that the window function operator satisfies criteria, wherein the criteria include that the window function operator applies no framing, has no ORDER BY clause, and no PARTITION BY clause.

10. The method of claim 8 , wherein rewriting the particular query is performed in response to determining that the window function operator satisfies criteria, wherein the criteria include that the window function operator applies no framing, has no ORDER BY clause, and an empty OVER clause.

11. The method of claim 8 , wherein the Cartesian product operator is a join operator with empty join attributes.

12. The method of claim 8 , wherein the pure aggregation operator is a GROUP BY operator with a single grouping.

13. The method of claim 8 , wherein the pure aggregation operator and the Cartesian product operator share the intermediate relation as an input.

14. The method of claim 8 , wherein the intermediate relation is materialized as a temporary result, and the pure aggregation operator and the Cartesian product operator, when executed, read from the temporary result.

15. The method of claim 8 , further comprising executing the rewritten query on a dataset to obtain a result.

16. 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:
a DBMS (database management system) rewriting a particular query to generate a rewritten query, wherein the particular query specifies a window function operator, a particular input to the window function operator, and an analytical function, wherein rewriting the particular query includes:
assigning the particular input to an intermediate relation;
replacing the window function operator with a replacement operator, and assigning to the replacement operator:
an aggregate function corresponding to the analytical function, and
the intermediate relation;

placing a join operator that joins the intermediate relation with an output of the replacement operator;

said DBMS executing said rewritten query.

17. The one or more non-transitory computer-readable storage medium of claim 16 , wherein rewriting the particular query is performed in response to determining that the window function operator satisfies criteria, wherein the criteria include that the window function operator applies no framing, has no ORDER BY clause, and includes one or more PARTITION BY attributes.

18. The one or more non-transitory computer-readable storage medium of claim 17 , wherein the replacement operator is a GROUP BY operator, and replacing the window function operator with the replacement operator further includes assigning to the GROUP BY operator the one or more PARTITION BY attributes as grouping attributes; and
wherein the join operator includes the one or more PARTITION BY attributes as one or more join attributes.

19. The one or more non-transitory computer-readable storage medium of claim 16 , wherein rewriting the particular query is performed in response to determining that the window function operator satisfies criteria, wherein the criteria include that the window function operator applies no framing, has no ORDER BY clause, and no PARTITION BY clause.

20. The one or more non-transitory computer-readable storage medium of claim 19 , wherein the replacement operator is a pure aggregation operator and the join operator is a Cartesian product operator.

21. A method, comprising:
a DBMS (database management system) rewriting a particular query to generate a rewritten query, wherein the particular query specifies a window function operator, a particular input to the window function operator, and an analytical function, wherein rewriting the particular query includes:
assigning the particular input to an intermediate relation;
replacing the window function operator with a replacement operator, and assigning to the replacement operator:
an aggregate function corresponding to the analytical function, and the intermediate relation;

placing a join operator that joins the intermediate relation with an output of the replacement operator;

said DBMS executing said rewritten query.

22. The method of claim 21 , wherein rewriting the particular query is performed in response to determining that the window function operator satisfies criteria, wherein the criteria include that the window function operator applies no framing, has no ORDER BY c

Assignees

• Oracle Int Corp

Inventors

• Fender Pit
• Schlegel Benjamin
• Brantner Matthias

Classifications

• G06F16/2456

  Join operations · CPC title

• G06F16/24535 Primary

  of sub-queries or views · CPC title

• G06F16/24537

  of operators · CPC title

• G06F16/24556 Primary

  Aggregation; Duplicate elimination · CPC title

• G06F16/24539

  using cached or materialised query results · CPC title