Minimizing processing using an index when non-leading columns match an aggregation key

Patent metadata

Field	Value
Publication number	US-11010380-B2
Application number	US-201815895423-A
Country	US
Kind code	B2
Filing date	Feb 13, 2018
Priority date	Feb 13, 2018
Publication date	May 18, 2021
Grant date	May 18, 2021

Abstract

Provided are techniques for minimizing processing using an index when non-leading columns match an aggregation key. A query that includes a clause and an aggregation key is received. It is determined that a non-leading key column of an index matches a column of the aggregation key comprising any of: a leading column of an ORDER BY aggregation key, any column of a duplicate removal aggregation key, a MIN aggregation key, and a MAX aggregation key. The clause is processed using an order of the aggregation key to generate a first result set. Then, the clause is processed using the non-leading key column of the index that matched the column of the aggregation key and the first result set to generate a second result set. The second result set is returned.

First claim

What is claimed is:

1. A computer program product, the computer program product comprising a computer readable storage medium having program code embodied therewith, the program code executable by at least one processor to perform operations for:
receiving a query that includes a clause and an aggregation key, wherein the aggregation key comprises columns;
searching through each index of a plurality of indexes for one of: a first column of a sort aggregation key, any column of a grouping aggregation key, and any column of a duplicate removal aggregation key;
in response to finding an index that has a leading column that matches one of: the first column of the sort aggregation key, any column of the grouping aggregation key, and any column of the duplicate removal aggregation key, performing a partial ordering approach; and
in response to finding an index that has a non-leading column that matches one of: the first column of the sort aggregation key, any column of the grouping aggregation key, and any column of the duplicate removal aggregation key, performing a minimizing aggregation approach by:
processing the clause using an order of columns of the index to generate a first result set;
processing the clause using non-leading key columns of the index that matched the columns of the aggregation key and the first result set to generate a second result set; and
returning the second result set.

2. The computer program product of claim 1 , wherein the clause comprises a duplicate removal clause that is any of a GROUP BY clause, a DISTINCT clause, a UNION clause, and a non-correlated subquery.

3. The computer program product of claim 1 , wherein the program code is executable by the at least one processor to perform further operations for:
performing duplicate skipping when searching the index to generate the first result set.

4. The computer program product of claim 1 , wherein the program code is executable by the at least one processor to perform further operations for:
performing parallel processing with child tasks reporting to parent tasks.

5. The computer program product of claim 1 , wherein, for the partial ordering approach, a partial order provided by the index is used to reduce processing for the aggregation key.

6. The computer program product of claim 1 , wherein a Software as a Service (SaaS) is configured to perform the operations of the computer program product.

7. A computer system, comprising:
one or more processors, one or more computer-readable memories and one or more computer-readable, tangible storage devices; and
program instructions, stored on at least one of the one or more computer-readable, tangible storage devices for execution by at least one of the one or more processors via at least one of the one or more computer-readable memories, to perform operations comprising:
receiving a query that includes a clause and an aggregation key, wherein the aggregation key comprises columns;
searching through each index of a plurality of indexes for one of: a first column of a sort aggregation key, any column of a grouping aggregation key, and any column of a duplicate removal aggregation key;
in response to finding an index that has a leading column that matches one of: the first column of the sort aggregation key, any column of the grouping aggregation key, and any column of the duplicate removal aggregation key, performing a partial ordering approach; and
in response to finding an index that has a non-leading column that matches one of: the first column of the sort aggregation key, any column of the grouping aggregation key, and any column of the duplicate removal aggregation key, performing a minimizing aggregation approach by:
processing the clause using an order of columns of the index to generate a first result set;
processing the clause using non-leading key columns of the index that matched the columns of the aggregation key and the first result set to generate a second result set; and
returning the second result set.

8. The computer system of claim 7 , wherein the clause comprises a duplicate removal clause that is any of a GROUP BY clause, a DISTINCT clause, a UNION clause, and a non-correlated subquery.

9. The computer system of claim 7 , wherein the operations further comprise:
performing duplicate skipping when searching the index to generate the first result set.

10. The computer system of claim 7 , wherein the operations further comprise:
performing parallel processing with child tasks reporting to parent tasks.

11. The computer system of claim 7 , wherein, for the partial ordering approach, a partial order provided by the index is used to reduce processing for the aggregation key.

12. The computer system of claim 7 , wherein a Software as a Service (SaaS) is configured to perform the operations of the computer system.

Assignees

• IBM

Inventors

• Purcell Terence P
• Beavin Thomas A

Classifications

• G06F16/24554

  Unary operations; Data partitioning operations · CPC title

• G06F16/24542 Primary

  Plan optimisation · CPC title

• G06F16/248

  Presentation of query results · CPC title