MINIMIZING PROCESSING USING AN INDEX WHEN NON-LEADING COLUMNS MATCH AN AGGREGATION KEY

• US 20190324965A1
• Filed: 06/20/2019
• Published: 10/24/2019
• Est. Priority Date: 02/13/2018
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Purcell, Terence P., Beavin, Thomas A.
• Granted Patent: US 11,048,703 B2
• Time in Patent Office: Days
• CPC Class Codes: G06F 16/24542 Plan optimisation
• CPC Class Codes: G06F 16/24554 Unary operations; Data partitioning operations
• CPC Class Codes: G06F 16/248 Presentation of query results

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.

Claims

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;

determining, with the processor of the computer, 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;

processing, with the processor of the computer, the clause using an order of the aggregation key to generate a first result set; and

processing, with the processor of the computer, the clause 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; 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, further comprising operations for:

determining whether to perform a minimizing aggregation approach.

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

7-18. -18. (canceled)