Reordering of complex SQL queries involving groupbys, joins, outer joins and full outer joins
First Claim
1. A method of simplifying a query performed by a computer to retrieve data from a relational database stored in a electronic storage device coupled to the computer, the method comprising the steps of:
- (a) eliminating redundant sub-expressions from the query and modifying expensive binary operations in the query to inexpensive binary operations, so that only simple and complex predicates remain in the query; and
(b) converting the complex predicates remaining in the query to simple predicates by application of a generalized selection (GS) operator.
0 Assignments
0 Petitions
Accused Products
Abstract
A method, apparatus, and article of manufacture for query simplification by applying generalized inference propagation and generalized transitive closure in SQL queries having selection, projection, join, outer join, and intersection operations. The disclosed transformations and enumeration method unify and solve the problems of 1) unnesting join aggregate queries, and 2) complete enumeration of queries containing outer joins, when the outer join predicate references an aggregated value, or the predicate references more than two base relations in a query subtree. The system first eliminates redundant sub-expressions and modifies expensive binary operations to inexpensive binary operations, then converts complex predicates to simple predicates by application of a generalized selection (GS) operator.
54 Citations
51 Claims
-
1. A method of simplifying a query performed by a computer to retrieve data from a relational database stored in a electronic storage device coupled to the computer, the method comprising the steps of:
-
(a) eliminating redundant sub-expressions from the query and modifying expensive binary operations in the query to inexpensive binary operations, so that only simple and complex predicates remain in the query; and (b) converting the complex predicates remaining in the query to simple predicates by application of a generalized selection (GS) operator. - View Dependent Claims (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17)
-
-
18. An apparatus for simplifying a query performed by a computer to retrieve data from a relational database stored in a electronic storage device, comprising:
-
(a) means for eliminating redundant sub-expressions from the query and modifying expensive binary operations in the query to inexpensive binary operations, so that only simple and complex predicates remain in the query; and (b) means for converting the complex predicates remaining in the query to simple predicates by application of a generalized selection (GS) operator. - View Dependent Claims (19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34)
-
-
35. An article of manufacture comprising a computer program carrier embodying one or more instructions of a method of simplifying a query performed by a computer to retrieve data from a relational database stored in a electronic storage device, the method comprising the steps of:
-
(a) eliminating redundant sub-expressions from the query and modifying expensive binary operations in the query to inexpensive binary operations, so that only simple and complex predicates remain in the query; and (b) converting the complex predicates remaining in the query to simple predicates by application of a generalized selection (GS) operator. - View Dependent Claims (36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51)
-
Specification