Query transformation for union all view join queries using join predicates for pruning and distribution
First Claim
Patent Images
1. A method of optimizing a query in a computer system, the query being performed by the computer system to retrieve data from a database stored on the computer system, the method comprising:
- (a) combining join predicates from a query with local predicates from each branch of one or more UNION ALL views referenced by the query, wherein the query joins two or more of the UNION ALL views, each UNION ALL view contains a UNION ALL of one base table with a local predicate specifying a data range in the base table, and the join predicates are on columns of the UNION ALL views that correspond to the local predicates;
(b) analyzing the combined predicates; and
(c) generating a join, except when the analyzing step indicates that the combined predicates are always FALSE and the join generates an empty result.
2 Assignments
0 Petitions
Accused Products
Abstract
A method, apparatus, and article of manufacture for optimizing a query in a computer system, wherein the query is performed by the computer system to retrieve data from a database stored on the computer system. The optimization includes: (a) combining join predicates from a query with local predicates from each branch of one or more UNION ALL views referenced by the query; (b) analyzing the combined predicates; and (c) not generating the join when the analysis step indicates that the combined predicates lead to an empty result.
-
Citations
30 Claims
-
1. A method of optimizing a query in a computer system, the query being performed by the computer system to retrieve data from a database stored on the computer system, the method comprising:
-
(a) combining join predicates from a query with local predicates from each branch of one or more UNION ALL views referenced by the query, wherein the query joins two or more of the UNION ALL views, each UNION ALL view contains a UNION ALL of one base table with a local predicate specifying a data range in the base table, and the join predicates are on columns of the UNION ALL views that correspond to the local predicates; (b) analyzing the combined predicates; and (c) generating a join, except when the analyzing step indicates that the combined predicates are always FALSE and the join generates an empty result. - View Dependent Claims (2, 3, 4, 5, 6, 7, 8, 9, 10)
-
-
11. A computer-implemented apparatus for optimizing a query, comprising:
-
(a) a computer system; (b) means, performed by the computer system, for; (1) combining join predicates from a query with local predicates from each branch of one or more UNION ALL views referenced by the query, wherein the query joins two or more of the UNION ALL views, each UNION ALL view contains a UNION ALL of one base table with a local predicate specifying a data range in the base table, and the join predicates are on columns of the UNION ALL views that correspond to the local predicates; (2) analyzing the combined predicates; and (3) generating a join, except when the analyzing step indicates that the combined predicates are always FALSE and the join generates an empty result. - View Dependent Claims (12, 13, 14, 15, 16, 17, 18, 19, 20)
-
-
21. An article of manufacture comprising a computer-readable storage media embodying instructions that, when read and executed by a computer system, results in the computer system performing a method for optimizing a query, the query being performed by the computer system to retrieve data from a database stored in a data storage device coupled to the computer system, the method comprising:
-
(a) combining join predicates from a query with local predicates from each branch of one or more UNION ALL views referenced by the query, wherein the query joins two or more of the UNION ALL views, each UNION ALL view contains a UNION ALL of one base table with a local predicate specifying a data range in the base table, and the join predicates are on columns of the UNION ALL views that correspond to the local predicates; (b) analyzing the combined predicates; and (c) generating a join, except when the analyzing step indicates that the combined predicates are always FALSE and the join generates an empty result. - View Dependent Claims (22, 23, 24, 25, 26, 27, 28, 29, 30)
-
Specification