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

Patent metadata

Field	Value
Publication number	US-11048703-B2
Application number	US-201916447658-A
Country	US
Kind code	B2
Filing date	Jun 20, 2019
Priority date	Feb 13, 2018
Publication date	Jun 29, 2021
Grant date	Jun 29, 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-implemented method, comprising operations for:
receiving, with a processor of a computer, 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, with the processor of the computer, the clause using an order of columns of the index to generate a first result set; and
processing, with the processor of the computer, 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, with the processor of the computer, the second result set.

2. The computer-implemented method 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-implemented method of claim 1 , further comprising operations for:
performing duplicate skipping when searching the index to generate the first result set.

4. The computer-implemented method of claim 1 , further comprising operations for:
performing parallel processing with child tasks reporting to parent tasks.

5. The computer-implemented method 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-implemented method of claim 1 , wherein a Software as a Service (SaaS) is configured to perform the operations of the method.

Assignees

• IBM

Inventors

• Purcell Terence P
• Beavin Thomas A

Classifications

• G06F16/24542 Primary

  Plan optimisation · CPC title

• G06F16/24554

  Unary operations; Data partitioning operations · CPC title

• G06F16/248

  Presentation of query results · CPC title