Outer join optimizations in database management systems

Patent metadata

Field	Value
Publication number	US-10127277-B2
Application number	US-201514814544-A
Country	US
Kind code	B2
Filing date	Jul 31, 2015
Priority date	Jul 31, 2015
Publication date	Nov 13, 2018
Grant date	Nov 13, 2018

Abstract

In one embodiment, a method of processing a structured query language (SQL) statement is provided, comprising: determining whether a first query and a second query are equivalent, the first and second queries being respectively the left side and the right side operands of the OUTER JOIN operation; determining whether a SELECT output of the SQL statement does not refer to database columns from the left side of the OUTER JOIN operation which are not also partitioning columns of the OUTER JOIN operation; and responsive to determining that the first query and the second query are equivalent and that the SELECT output of the SQL statement does not refer to database columns from the left side of the OUTER JOIN operation which are not also partitioning columns of the OUTER JOIN operation, transforming the SQL statement into an optimized query SQL statement by removing the OUTER JOIN operation.

First claim

What is claimed is:

1. A computer program product for processing a structured query language (SQL) statement, the SQL statement comprising at least an OUTER JOIN operation, the computer program product comprising:
one or more computer readable storage media and program instructions stored on the one or more computer readable storage media, the program instructions comprising program instructions to:
determine whether a first query and a second query are equivalent, the first and second queries being respectively the left side and the right side operands of the OUTER JOIN operation;
determine whether an output of the right side of the OUTER JOIN operation contains an output of the left side of the OUTER JOIN operation;
determine whether partitioning columns of a GROUP BY operation are the same as partitioning columns of the OUTER JOIN operation;
determine whether columns quantified by the right side of the OUTER JOIN operation include one or both of multiple distinct aggregation operations or multiple aggregation operations;
determine whether there are no filter predicates or having clause in the GROUP BY operation;
determine whether a SELECT output of the SQL statement does not refer to database columns from the left side of the OUTER JOIN operation which are not also partitioning columns of the OUTER JOIN operation; and
responsive to determining that: (i) output of the first side of the OUTER JOIN operation contains the output of the left side of the OUTER JOIN operation, (ii) the partitioning columns of the GROUP BY operation are the same as the partitioning columns of the OUTER JOIN operation, (iii) the columns quantified by the right side of the OUTER JOIN operation include one or both of multiple distinct aggregation operations or multiple aggregation operations, (iv) there are no filter predicates or having clause in the GROUP BY operation, (v) the first query and the second query are equivalent, and (vi) the SELECT output of the SQL statement does not refer to database columns from the left side of the OUTER JOIN operation which are not also partitioning columns of the OUTER JOIN operation, transform the SQL statement into an optimized query SQL statement by removing the OUTER JOIN operation.

2. The computer program product of claim 1 , wherein the OUTER JOIN operation is one of a FULL OUTER JOIN, a LEFT OUTER JOIN or a RIGHT OUTER JOIN.

3. The computer program product of claim 1 , further comprising program instructions to:
determine whether the partitioning columns of the GROUP BY operation quantified by the right side of the OUTER JOIN operation are the same as the partitioning columns of the GROUP BY operation quantified by the left side of the OUTER JOIN operation; and
wherein said OUTER JOIN operation is removed from the query only in response to determining that the partitioning columns of the GROUP BY operation quantified by the right side of the OUTER JOIN operation are the same as the partitioning columns of the GROUP BY operation quantified by the left side of the OUTER JOIN operation.

4. The computer program product of claim 3 , wherein the OUTER JOIN operation is one of a FULL OUTER JOIN, a LEFT OUTER JOIN or a RIGHT OUTER JOIN.

5. The computer program product of claim 1 , wherein the OUTER JOIN operation is represented by a RIGHT OUTER JOIN, and further comprising program instructions to:
determine whether the output quantified by the left side of the OUTER JOIN does not match the output quantified by the right side of the OUTER JOIN;
determine whether the output of the right side of the OUTER JOIN operation subsumes the output of the left side of the OUTER JOIN operation;
determine whether partitioning columns of a GROUP BY operation are the same as the portioning columns of the OUTER JOIN operation;
determine whether there are no filter predicates or having clause in the GROUP BY operation; and
responsive to determining that the output quantified by the left side of the OUTER JOIN does not match the output quantified by the right side of the OUTER JOIN, that the output of the right side of the OUTER JOIN operation contains the output of the left side of the OUTER JOIN operation, that the partitioning columns of the GROUP BY operation are the same as the OUTER JOIN partitioning columns and that the columns quantified by the right side of the OUTER JOIN operation include one or both of multiple distinct aggregation operations or multiple aggregation operations, and that there are no filter predicates or having clause in the GROUP BY operation, transform the SQL statement into an optimized query SQL statement by removing the OUTER JOIN operation.

6. The computer program product of claim 1 , further comprising program instructions to:
determine whether the partitioning columns of a GROUP BY operation are the same as the partitioning columns of the OUTER JOIN operation;
determine whether the columns quantified by the right side of the OUTER JOIN include one or more of multiple distinct aggregation operations or multiple aggregation operations;
determine whether a filter predicate can be evaluated after the OUTER JOIN operation is removed; and
responsive to determining that the partitioning columns of the GROUP BY operation are the same as the OUTER JOIN Partitioning columns, that the columns quantified by the right side of the OUTER JOIN include one or more of multiple distinct aggregation operations or multiple aggregation operations, and that the filter predicate can be evaluated after the OUTER JOIN operation is removed, transform the SQL statement into an optimized query SQL statement by removing the OUTER JOIN operation.

7. The computer program product of claim 6 , wherein:
the OUTER JOIN operation is represented by a LEFT OUTER JOIN;
the SQL statement has a HAVING clause on the multiple distinct aggregation output; and further comprising program instructions to:
determine whether the HAVING clause in the GROUP BY operation can be evaluated after the OUTER JOIN operation is removed; and
further responsive to determining that the HAVING clause in the GROUP BY operation can be evaluated after the OUTER JOIN operation is removed, transform the SQL statement into an optimized query SQL statement by removing the OUTER JOIN operation.

8. A computer system for processing a structured query language (SQL) statement, the SQL statement comprising at least an OUTER JOIN operation, the computer system comprising:
one or more computer processors;
one or more computer readable storage media; and
program instructions stored on the one or more computer readable storage media for execution by at least one of the one or more processors, the program instructions comprising program instructions to:
determine whether a first query and a second query are equivalent, the first and second queries being respectively the left side and the right side operands of the OUTER JOIN operation;
determine whether an output of the right side of the OUTER JOIN operation contains an output of the left side of the OUTER JOIN operation;
determine whether partitioning columns of a GROUP BY operation are the same as partitioning columns of the OUTER JOIN operation;
determine whether columns quantified by the right side of the OUTER JOIN operation include one or both of multiple distinct aggregation operations or multiple aggregation operations;
determine whether there are no filter predicates or having clause in the GROUP BY operation;
determine whether a SELECT output of the SQL statement does not refer to database columns from the left side of the OUTER JOIN operation which are not also partitioning columns of the OUTER JOIN operation; and
responsive to determining that: (i) output of the first side of the OUTER JOIN operation contains the

Assignees

• IBM

Inventors

• Barbas Pedro M
• Duffy Peter J
• Pan Lei
• Tilson David M

Classifications

• G06F16/24537 Primary

  of operators · CPC title

• G06F17/30454 Primary

  Physics · mapped topic