Granted Patent B2

US 11,023,468 · App. 14/947,627 · Granted Jun 1, 2021

Inventors: Christoph Weyerhaeuser (Heidelberg, DE); Tobias Mindnich (Sulzbach, DE); Johannes Merx (Heidelberg, DE); Julian Schwing (Mannheim, DE)

Assignee: SAP SE

G06F16/24554

Abstract

Claims (36)

1. A method comprising:

receiving, by a calculation engine, a query to optimize table scanning when the query includes a first aggregation query instruction over at least a first group and a first key figure column and a last aggregation query instruction over at least a second group and a second key figure column, the calculation engine interfacing at least one database including a table;

detecting, by the calculation engine, that the query includes the first aggregation query instruction and the last aggregation query instruction, wherein the first aggregation query instruction is configured to return, as part of a result set of the query, a first key figure value in the first group, and wherein the last aggregation query instruction is configured to return, as part of the result set of the query, a last key figure value in the second group;

in response to the detecting that the received query includes the first aggregation query instruction and the last aggregation query instruction, optimizing, by the calculation engine, the received query, wherein the optimizing further comprises initiating execution of the received query by at least:

performing, by the calculation engine, a single read of the table,

replacing, in the single read table, attribute values of the first group and the second group with corresponding first hash values for the first group and corresponding second hash values for the second group;

detecting, from the single read table, the first group and the second group based on a change in the hash values, and

indicating, in the detected first group and the second group, the first key figure value corresponding to the first aggregation query instruction and the last key figure value corresponding to the last aggregation query instruction; and

returning the result set to the query, the result set including the indicated first key figure value and the indicated last key figure value, without the calculation engine performing a second read of the table.

2. The method of claim 1 , wherein the detecting further comprises:

detecting, from the single table read, the at least one group based on the hash value.

3. The method of claim 1 , wherein the query includes a plurality of first aggregation query instructions and a plurality of last aggregation query instructions over a plurality of groups and a plurality key figures.

4. The method of claim 1 , wherein the database includes a database optimizer separate from an optimizer of the calculation engine interfacing the database.

5. The method of claim 1 , wherein the first group and the second group are each sorted.

6. A system comprising:

at least one processor and at least one memory including code which when executed causes operations comprising:

receiving, by a calculation engine, a query to optimize table scanning when the query includes a first aggregation query instruction over at least a first group and a first key figure column and a last aggregation query instruction over at least a second group and a second key figure column, the calculation engine interfacing at least one database including a table;

detecting, by the calculation engine, that the query includes the first aggregation query instruction and the last aggregation query instruction, wherein the first aggregation query instruction is configured to return, as part of a result set of the query, a first key figure value in the first group, and wherein the last aggregation query instruction is configured to return, as part of the result set of the query, a last key figure value in the second group;

in response to the detecting that the received query includes the first aggregation query instruction and the last aggregation query instruction, optimizing, by the calculation engine, the received query, wherein the optimizing further comprises initiating execution of the received query by at least:

performing, by the calculation engine, a single read of the table,

replacing, in the single read table, attribute values of the first group and the second group with corresponding first hash values for the first group and corresponding second hash values for the second group;

detecting, from the single read table, the first group and the second group based on a change in the hash values, and

indicating, in the detected first group and the second group, the first key figure value corresponding to the first aggregation query instruction and the last key figure value corresponding to the last aggregation query instruction; and

returning the result set to the query, the result set including the indicated first key figure value and the indicated last key figure value, without the calculation engine performing a second read of the table.

7. The system of claim 6 , wherein the detecting further comprises:

detecting, from the single table read, the at least one group based on the hash value.

8. The system of claim 6 , wherein the query includes a plurality of first aggregation query instructions and a plurality of last aggregation query instructions over a plurality of groups and a plurality keyfigures.

9. A non-transitory computer-readable storage medium including program code which when executed causes operations comprising:

receiving, by a calculation engine, a query to optimize table scanning when the query includes a first aggregation query instruction over at least a first group and a first key figure column and a last aggregation query instruction over at least a second group and a second key figure column, the calculation engine interfacing at least one database including a table;

detecting, by the calculation engine, that the query includes the first aggregation query instruction and the last aggregation query instruction, wherein the first aggregation query instruction is configured to return, as part of a result set of the query, a first key figure value in the first group, and wherein the last aggregation query instruction is configured to return, as part of the result set of the query, a last key figure value in the second group;

in response to the detecting that the received query includes the first aggregation query instruction and the last aggregation query instruction, optimizing, by the calculation engine, the received query, wherein the optimizing further comprises initiating execution of the received query by at least:

performing, by the calculation engine, a single read of the table,

replacing, in the single read table, attribute values of the first group and the second group with corresponding first hash values for the first group and corresponding second hash values for the second group;

detecting, from the single read table, the first group and the second group based on a change in the hash values, and

indicating, in the detected first group and the second group, the first key figure value corresponding to the first aggregation query instruction and the last key figure value corresponding to the last aggregation query instruction; and

returning the result set to the query, the result set including the indicated first key figure value and the indicated last key figure value, without the calculation engine performing a second read of the table.

Assignments (1)

Continuity (1)

Cited By (35)

US 12,190,069 US 12,210,841 US 12,210,843 US 12,217,009 US 12,217,010 US 12,223,285 US 12,223,286 US 12,223,287 US 12,236,199 US 12,242,812 US 12,242,813 US 12,242,814 US 12,254,277 US 12,254,278 US 12,260,181 US 12,260,182 US 12,314,660 US 12,321,697 US 12,340,180 US 12,353,827 US 12,393,777 US 12,400,085 US 12,406,146 US 12,430,503 US 12,430,504 US 12,430,505 US 12,456,008 US 12,499,320 US 12,518,107 US 12,524,619 US 12,554,935 US 12,585,883 US 12,596,881 US 12,730,976 US 12,737,552