Efficient optimization of SQL queries having set operators with a multi-set semantic

Patent metadata

Field	Value
Publication number	US-11650991-B2
Application number	US-202017107874-A
Country	US
Kind code	B2
Filing date	Nov 30, 2020
Priority date	Nov 30, 2020
Publication date	May 16, 2023
Grant date	May 16, 2023

Abstract

Disclosed is a system, method, and computer program product to efficiently process multi-set operations in a database system. An approach is described to perform a group-by operation with a counter to efficiently process such queries. Techniques are described to optimize multi-set operations into regular-set operations.

First claim

What is claimed is:

1. A method for optimizing a database query, comprising:
receiving a database query having a SET operator with a multi-set semantic;
processing the SET operator in the database query into at least a first GROUP-BY operation, a second GROUP-BY operation, and a correlation operation, wherein
the first GROUP-BY operation is performed on a first table column of a first database table referenced by the SET operator with the multi-set semantic, and a first counter for a first result associated with the first GROUP-BY operation is maintained, and
the second GROUP-BY operation is performed on a second table column of a second database table referenced by the SET operator with the multi-set semantic, and a second counter for a second result associated with the second GROUP-BY operation is maintained;

performing the correlation operation on the first and second counters to generate a correlation between the first result with the second result; and
generating a result set for the SET operator based at least in part upon the correlation, wherein a total number of instances for at least a row value in the result set is determined by using at least the correlation; and
generating a query result set based at least in part upon the result set for the SET operator.

2. The method of claim 1 , further comprising performing the correlation operation on the first counter and the second counter using an operator that is determined based at least in part upon the SET operator, wherein the first and second GROUP-BY operations are sort GROUP-BY operations.

3. The method of claim 1 , wherein the SET operator having the multi-set semantic is a MINUS ALL operator, and the first result and the second result are correlated by calculating a maximum of either zero or a difference between the first counter and the second counter.

4. The method of claim 1 , wherein the SET operator having the multi-set semantic is an INTERSECT ALL operator, and the first and second results are correlated by calculating a minimum between the first counter and the second counter.

5. The method of claim 1 , wherein the first and second GROUP-BY operations are hashed GROUP-BY operations respectively followed by a separate sorting operation.

6. The method of claim 1 , wherein the SET operator having the multi-set semantic is converted to a regular set operator.

7. The method of claim 6 , wherein at least one of the following conversions occurs in the database query: (a) INTERSECT ALL is converted to INTERSECT when a first subquery includes INTERSECT, MINUS, or UNION; (b) MINUS ALL is converted to MINUS when a second subquery includes INTERSECT, MINUS, or UNION on a left-hand branch of the database query; or (c) INTERSECT ALL is converted to INTERSECT, and MINUS ALL is converted to MINUS when a parent branch includes INTERSECT, MINUS, or UNION.

8. A system for optimizing a database query, comprising:
a processor;
a memory for holding programmable code; and
wherein the programmable code includes instructions executable by the processor for
receiving a database query having a SET operator with a multi-set semantic;
processing the SET operator in the database query into at least a first GROUP-BY operation, a second GROUP-BY operation, and a correlation operation, wherein
the first GROUP-BY operation is performed on a first table column of a first database table referenced by the SET operator with the multi-set semantic, and a first counter for a first result associated with the first GROUP-BY operation is maintained, and
the second GROUP-BY operation is performed on a second table column of a second database table referenced by the SET operator with the multi-set semantic, and a second counter for a second result associated with the second GROUP-BY operation is maintained;

performing the correlation operation on the first and second counters to generate a correlation between the first result with the second result; and
generating a result set for the SET operator based at least in part upon the correlation, wherein a total number of instances for at least a row value in the result set is determined by using at least the correlation; and
generating a query result set based at least in part upon the result set for the SET operator.

9. The system of claim 8 , wherein the programmable code further includes instructions executable by the processor for performing the correlation operation on the first counter and the second counter using an operator that is determined based at least in part upon the SET operator, and the first and second GROUP-BY operations are sort GROUP-BY operations.

10. The system of claim 8 , wherein the SET operator having the multi-set semantic is a MINUS ALL operator, and the first and the second results are correlated by calculating a maximum of either zero or a difference between the first counter and the second counter.

11. The system of claim 8 , wherein the SET operator having the multi-set semantic is an INTERSECT ALL operator, and the first and the second results are correlated by calculating a minimum between the first counter and the second counter.

12. The system of claim 8 , wherein the first and second GROUP-BY operations are hashed GROUP-BY operations respectively followed by a separate sorting operation.

13. The system of claim 8 , wherein the SET operator having the multi-set semantic is converted to a regular set operation.

14. The system of claim 13 , wherein at least one of the following conversions occur in the database query: (a) INTERSECT ALL is converted to INTERSECT when a first subquery includes INTERSECT, MINUS, or UNION; (b) MINUS ALL is converted to MINUS when a second subquery includes INTERSECT, MINUS, or UNION on a left-hand branch of the database query; or (c) INTERSECT ALL is converted to INTERSECT and MINUS ALL is converted to MINUS when a parent branch includes INTERSECT, MINUS, or UNION.

15. A computer program product embodied on a non-transitory computer readable medium, the non-transitory computer readable medium having stored thereon a sequence of instructions which, when executed by a processor, executes a set of acts for optimizing a database query, the set of acts comprising:
receiving a database query having a SET operator with a multi-set semantic;
processing the SET operator in the database query at least a first GROUP-BY OPERATION, a second GROUP-BY operation, and a correlation operation, wherein
the first GROUP-BY operation is performed on a first table column of a first database table referenced by the SET operator with the multi-set semantic, and a first counter for a first result associated with the first GROUP-BY operation is maintained, and
the second GROUP-BY operation is performed on a second table column of a second database table referenced by the SET operator with the multi-set semantic, and a second counter for a second result associated with the second GROUP-BY operation is maintained;

performing the correlation operation on the first and second counters to generate a correlation between the first result with the second result; and
generating a result set for the SET operator based at least in part upon the correlation, wherein a total number of instances for at least a row value in the result set is determined by using at least the correlation; and
generating a query result set based at least in part upon the result set for the SET operator.

16. The computer program product of claim 15 , wherein the set of acts further comprises performing the correlation operation on the first counter and the second counter using an operator that is de

Assignees

• Oracle Int Corp

Inventors

• Bellamkonda Srikanth
• Su Yu

Classifications

• G06F16/248

  Presentation of query results · CPC title

• G06F16/221

  Column-oriented storage; Management thereof · CPC title

• G06F16/244

  Grouping and aggregation · CPC title

• G06F16/2282

  Tablespace storage structures; Management thereof · CPC title

• G06F16/2456 Primary

  Join operations · CPC title