Join factorization of union/union all queries

Abstract

Under a type of query transformation referred to herein as join factorization, the branches of an UNION/UNION ALL query that join a common table are combined to reduce accesses to the common table. The transformation can be expressed as (T1 join T2) union all (T1 join T3)=T1 join (T2 union all T3), where T1, T2 and T3 are three tables. A given query may be rewritten in many alternate ways using join factorization. Evaluating each alternative can be expensive. Therefore, the alternatives are generated and evaluated in a way that minimizes the cost of evaluating the alternatives.

Description

BRIEF DESCRIPTION OF THE DRAWINGS

The present invention is illustrated by way of example, and not by way of limitation, in the figures of the accompanying drawings and in which like reference numerals refer to similar elements and in which:

FIG. 1 is a diagram of a query optimizer according to an embodiment of the present invention.

FIG. 2 is a flow chart depicting a procedure for generating join factorization units according to an embodiment of the present invention.

FIG. 3A is a flow chart depicting a procedure for search space analysis according to an embodiment of the present invention.

FIG. 3B is a flow chart depicting a procedure for search space analysis according to an embodiment of the present invention.

FIG. 4 is a diagram of computer system that may be used in an implementation of an embodiment of the present invention.

Claims

1. A computer implemented method, comprising: transforming a base query to generate a transformed query;wherein the base query includes a union between each base branch of a plurality of base branches;wherein two or more base branches of said plurality of base branches joins a set of tables;wherein the sets of tables of two or more base branches of the plurality of base branches includes a common table set shared by all the sets of tables, said common table set including common table; andwherein the step of transforming the base query includes replacing the plurality of base branches with a first group branch that joins the common table and an inline view, the inline view comprising a union between a plurality of respective branches, wherein the plurality of respective branches includes, for each base branch of said plurality of base branches, a FROM list that: references the respective set of tables that are not in the common table set, and does not reference the common table.

2. The method of claim 1, wherein each base branch of said plurality of base branches includes a respective join condition based on a particular column of said common table and a particular join condition operator;wherein the first group branch includes a join condition based on the join condition operator, a column of the inline view, and the column of the common table; andwherein each branch of said plurality of respective branches excludes the respective join condition.

3. The method of claim 1, wherein each base branch of said plurality of base branches includes a respective join condition based on a particular column of said common table and a particular join condition operator; andwherein each respective branch of said plurality of respective branches includes said respective join condition.

4. The method of claim 1, wherein the common table set includes at least one other common table.

5. The method of claim 4, wherein the first group branch joins the common table, inline view, and the at least one other common table.

6. The method of claim 1, wherein: the base query includes a union between a set of base branches that include said plurality of base branches and at least another branch; andsaid transformed query includes said another branch.

7. The method of claim 1, wherein: the base query includes a union between a set of base branches that include said plurality of base branches and a second plurality of base branches;each base branch of said second plurality of base branches joins a set of tables;the sets of tables of the second plurality of base branches includes a second common table; andthe step of transforming the base query includes replacing the second plurality of base branches with a second group branch that joins the second common table and second inline view.

8. The method of claim 7, wherein at least one branch of the plurality of base branches joins the second common table.

9. The method of claim 1, wherein the plurality of base branches are contained with a query block within the base query.

10. The method of claim 1, wherein the base query contains a second plurality of branches within the union and the transformed query contains the same second plurality of branches.

11. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 1.

12. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 2.

13. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 3.

14. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 4.

15. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 5.

16. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 6.

17. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 7.

18. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 8.

19. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 9.

20. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 10.